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.

Dig Deeper on Microsoft SQL Server Installation

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.