Post reply

Moving Oracle Tables to SQL Server 2000

  • April 23, 2007 at 12:35 pm

    #68402

    I am attempting to move Oracle tables and data to a new SQL Server 2000 database.  I am using the DTS import wizard and everything works great, except any tables with a DATE field fail.  It is trying to insert the data into a datetime field.

    I think it is because SQL Server is interpreting the date as 11/4/102 when it should be 11/4/2002.  It is thinking that the date is out of range.  Here is the error that is created by the DTS Package.

    The number of failing rows exceeds the maximum speficied.Insert error, colmn 5 ('ADDED_DATE', DBTYPE_DBTIMESTAMP), status 6: Data overflow

    Invalid character value for cast specification.

    Any ideas for fixing this problem?

     

    Thanks!

     

    **Update, Access will actually import these fine using import table.  Then DTS can import these dates correctly into SQL Server using the small date field.  Now, if there is just some way to easily just select the 1000+ rows for my schema since it show all the USER, ALL, and WMSYS stuff.

  • April 23, 2007 at 3:11 pm

    #702032

    It's been a while since I imported from Oracle.  I think I used the TO_CHAR function to convert dates to strings in temp Oracle tables then imported into the SQL Server tables using CAST to change the column back to datetime.

    Greg

    Greg

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply