Q
Problem solve Get help with specific problems with your technologies, process and projects.

Importing Oracle database tables

Data migration and interoperability between SQL Server and Oracle can be tricky. Expert Kevin Kline discusses what to be mindful of when importing data.

I'm trying to import an Oracle database table into SQL Server 2005 but I keep getting an error.
 Error at Destination for Row number 82696. Errors encountered so far in this task: 1. Insert error, column 16, ('Birth_Dt', DBTYPE_DBTIMESTAMP), status 6: Data overflow. Invalid character value for cast specification.
Any suggestions on how to resolve this issue?
You have got a data type mismatch error. What ever the data type is for the column on the SQL Server target database, it is not able to properly accept the data from Oracle. Basically the problem is that SQL Server doesn't support the ANSI standard TIMESTAMP data type. So you'll get this problem any time you import from a database platform that does support the data type, such as Oracle or DB2.

If you're using DTS/SSIS, I suggest you perform a transformation on the column to convert it to VARCHAR of the...

appropriate length and make sure the target column on SQL Server is of the DATETIME data type. It should work fine from there since SQL Server will do an implicit conversion of any string data type to DATETIME.

 


Do you have comments on this Ask the Expert Q&A? Let us know.
This was last published in September 2005

Dig Deeper on Microsoft SQL Server Installation

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close