Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2004
    Posts
    4

    Unanswered: SOS!!!can not insert the field of smalldatetime with NULL

    Hello everyone,
    I am using DTS to transform data from foxpro.when it meets the date of NULL,it fails and says"Insert Error , column 4( 'Coloumn_name ',DBTYPE_DBTIMESTAMP), Status 6 data overflow ".
    And then i try to open the DTS Transform Data task and do a preview of the data from the 'Source' side.The Null values are treated as "1899-12-30".
    After reading something ,i try to write an ActiveX scripting Task to solve this problem using the function of "IsDate()",but it fails with exceptions.
    Now I don't know what to do.could anyone give me some suggestion?Anything will be appreciated!ThankX in advance!

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Using DTS, I would use an ActiveX script during your transformation, doing basically this:

    If IsNull(<Your FoxPro datetime field>) Then
    Let <Your Sql Server Field> = NULL
    ElseIf <Your FoxPro datetime field> = '18991230' Then
    Let <Your Sql Server Field> = NULL
    Else
    Let <Your Sql Server Field> = <Your FoxPro datetime field>
    End If

    Hope this helps
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Nov 2004
    Posts
    4
    Thank you for your reply!
    this afternoon, i tried to import the dates into sql server in your way.My ActiveX Scripting Task is as below:
    Function Main()
    temp=IsNull(DTSSource("column_name"))
    if temp>0 then
    DTSDestination("column_name")=DTSSource("column_na me")
    else
    DTSDestination("column_name")=Null
    end if
    End Function

    when i test this task,it failed with"Task interrupted and returned with DataPumpTask error" .i don't know why.
    then i add main=success(the meaning is like this)as the last line of my code,the task works fine,but the dates that it imports are all NULL.
    Now i am wondering whether it is the problem of the compatibility between foxpro and sql server(i am importing data from foxproVFP).

  4. #4
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    it seems that your source isn't a datetime. So, you should see what it is, then you can think about necessary conversions.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  5. #5
    Join Date
    Nov 2004
    Posts
    4
    that's not datetime??
    Oh,my God,i am confused now.
    i have checked the foxpro table to see which datatype it is this morning,it is really "datetime".Is there any method that i can check it in sqlserver?

  6. #6
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    To be start with, I'd convert your FoxPro datetime value into string to see, what your input is:
    CAST(<Your FoxPro DateTime Field> As VARCHAR(200))
    You may for test reasons also consider to write your string into a temporary text column. Then you can use SQL Server's IsDate function to check whether this string can be evaluated as DateTime, see this example
    SELECT
    ISDATE('february 39, 2002') AS 'february 39, 2002',
    ISDATE('1/1/2002') AS '1/1/2002',
    ISNUMERIC('abc') AS 'abc',
    ISNUMERIC('123') AS '123'
    Let me know, what you are getting here.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  7. #7
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Just another aspect: please notice that the range of valid date of a SmallDateTime is January 1, 1900 -...- June 6, 2079. So, if FoxPro gives you dates of 1899-12-30, its outside of these limits, and you should consider to use DateTime instead of SmallDateTime, or you convert this NULL replacement accordingly.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  8. #8
    Join Date
    Nov 2004
    Posts
    4
    Thank you for your reply, sir!
    i have found this problem several days before.And when i changed the datatype into "datetime",it then shows me with another invalid value.

  9. #9
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    So, your problem was actually to get dates outside the valid ranges?! Then, the IsDate function returns FALSE, so you need to have a data type DateTime, and your function should look like:
    Function Main()
    if IsDate(CAST(DTSSource("column_name") AS VARCHAR(200))>0 then
    DTSDestination("column_name")=DTSSource("column_na me")
    else
    DTSDestination("column_name")=Null
    end if
    End Function
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •