SQL Server Performance Forum – Threads Archive

Error While migrating data from Sql Server to orac

Problem Summary: We have a Stored Procedure which does the transfer of data from SQL Server to Oracle using SQL Server’s Linked Server feature. This stored procedure uses a generic four part query. First it gets a list of User Tables (related to application) from SQL Server and then creates SQL statement based on this tablename as given below, "INSERT INTO ‘ + @vLinkedServer + ‘..’ + @vTargetSchema + ‘.’ + @chTableName + ‘ SELECT * FROM ‘ + @chTableName" This works for all the table except tables which has columns of type NVARCHAR2 type. We used two types of drivers while creating LinkedServer and results as below, 1. When using Orale Provider OLE DB
Server: Msg 7317, Level 16, State 1, Line 1
OLE DB provider ‘OraOLEDB.Oracle’ returned an invalid schema definition.
OLE DB error trace [Non-interface error: OLE/DB provider returned an invalid schema definition.]. 2. When using Microsoft OLE DB Provider For Oracle
Server: Msg 7356, Level 16, State 1, Line 1
OLE DB provider ‘MSDAORA’ supplied inconsistent metadata for a column. Metadata information was changed at execution time.
OLE DB error trace [Non-interface error: Column ‘NAME’ (compile-time ordinal 2) of object ‘"DBADMIN"."IDEUSER"’ was reported to have a DBTYPE of 130 at compile time and 129 at run time]. Note: IDEUSER table contains three columns, ID (Integer), Name (NVARCHAR2) and DOB (DATE) Environment: Windows XP Prof SP 4, SQL Server 2000 If you could provide solution it will be great!!!!

BOL refers:
http://msdn.microsoft.com/library/en-us/trblsql/tr_reslsyserr_2_64zd.asp
http://msdn.microsoft.com/library/en-us/trblsql/tr_err_dblib_0pwz.asp
http://www.aspfaq.com/show.asp?id=2400 Fyi HTH. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>