November 20, 2013 at 6:28 am
We are running SQL Server 2012 EE on Windows Server 2008 R2 EE. In SQL Server, we have set up linked servers which links to our unix/oracle 10g instance. On SQL Server, we are executing SELECT INTO commands to load data into SQL Server. We are receiving some errors which are listed below. Just wondering if anyone has any input (links, articles) on the below errors or other methods to get the tables and data from Oracle into SQL Server. Most of the errors are with data types (clobs, blobs, etc). We are also doing research (googling).
ERROR - Cannot open the table ""schema"."table_name"" from OLE DB provider "OraOLEDB.Oracle" for
linked server "prod_dbname_schema".
The specified table or view does not exist or contains errors.
Issue -: SQL Server can not bring over Nested Tables ??????
ERROR --: Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "prod_dbname_schema" supplied
inconsistent metadata for a column. The column "XMLFILE" (compile-time ordinal 3) of
object ""schema"."table_name"" was reported to have a "DBCOLUMNFLAGS_ISNULLABLE" of 0 at
compile time and 32 at run time.
Issue --: The schema table has a CLOB data type in Oracle
ERROR --: Msg 7354, Level 16, State 1, Line 2
The OLE DB provider "OraOLEDB.Oracle" for linked server "prod_dbname_schema" supplied invalid metadata for
column "V_BASICSTUDENT". The data type is not supported.
Issue --: V_BASICSTUDENT SYS.XMLTYPE - Datatype issue
Thanks, Kevin
November 26, 2013 at 1:25 pm
Just wondering if anyone had any input on this issue?
Thanks, Kevin
November 27, 2013 at 1:59 am
I've had to migrate data from Oracle to SQL before, and I've found the only real reliable way was through SSIS, as it could handle the data type differences better than T-SQL could via linked server queries, as I could just never get it to work 100% reliably with T-SQL, whereas SSIS did the job. Sure, it got a bit ugly with having to test data type mappings, but once did it always worked.
November 27, 2013 at 2:57 am
Not sure if this helps, I've had some similar issues communicating between Oracle and SQL. I fixed them by using an Oracle product - Oracle Gateway - to link Oracle 10g to MSSQL. There is a free option, but I used the licensed version which was not cheap. Setup was reasonably simple. If you have the Oracle skills this could work for you...
November 27, 2013 at 4:54 am
Yes, and sometimes you can get better results using different versions of the Oracle Client too if you have to go down the linked server route. I remember at the time changing from the Oracle 8 to 9 client fixed some issues we had, but I still ended up having to use SSIS in the end.
November 27, 2013 at 5:49 am
I had to pull Oracle data into SQL Server years ago and didn't have SSIS available. I had to explicitly convert each column into the appropriate data type so it would populate. The MS data type map is at http://technet.microsoft.com/en-us/library/ms151817.aspx and the Oracle one is at http://docs.oracle.com/html/B10544_01/apa.htm.
January 8, 2014 at 12:10 pm
Ok, I am back to researching this issue. Thanks everyone for your responses.
It appears I was able to load Oracle Data (non xml) into SQL Server using the Linked Server method with the OpenQuery Function and pass-through query. However, I have discovered (read) that xml data types are not supported in distributed queries (linked servers).
So, I then tried using SSIS. I created a package, I created a Connection Manager using the "Native OLE DB\Oracle Provider for OLE DB" Provider. I then went to the OLE DB Source Editor, selected the OLE DB Connection Manager, the Data Access Mode (Table or View) and the Name of the table or view (actual Oracle Table) and clicked the Columns Page or Preview Button. The following error was returned:
Validation error. Data Flow Task: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E37 Description: "Table does not exist.". An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E37 Description: "Table does not exist.". An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Description: "ROW-00004: Invalid column datatype".
This error was also returned when I used the "Native OLE DB\Microsoft OLE DB Provider for Oracle" Provider.
Does anyone know of a way I can used the Microsoft SQL Server Products (2012) to load Oracle XML Datatypes into SQL Server without using a 3rd party product? We are using Oracle Client 11g Software.
Thanks for your time and effort in advance, Kevin
January 8, 2014 at 1:30 pm
I got around the "Table does not exist" error by changing the Data Access Mode to SQL Command and using SQL Code to access the table without the double quotes. Now, when I click on the Preview Button, the following error is returned:
Validation error. Data Flow Task: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E14 Description: "ROW-00004: Invalid column datatype".
Thanks, Kevin
January 9, 2014 at 9:25 am
Not sure if it is fixed yet but discusses about casting it as xml-type in openquery
January 10, 2014 at 7:38 am
Thanks Jo. This helped me get farther down the road. The fist sql statement below works. However, with the second sql statement, I am getting an "ORA-00932: inconsistent datatypes: expected - got CLOB". (I have tried the second sql statement with NCLOB, BLOB, etc.)
--Works:
--Use CAST to cast from xml to char. Then use CAST to cast from char to xml using Linked Server and OpenQuery() Function.
SELECT CAST(V_BASICSTUDENT AS XML) V_BASICSTUDENT
FROM OPENQUERY (
Linked_DB_Test,
'SELECT CAST(V_BASICSTUDENT AS char) V_BASICSTUDENT
FROM TESTUSER.BASICSTUDENT
WHERE rownum <= 7'
) AS s;
GO
--Not Working:
--The SYS_NC_ROWINFO$ Column is xmltype with HUGECLOB in the data columns (when looking at the data using TOAD.)
--Use CAST to cast from xml to char. Then use CAST to cast from char to xml using Linked Server and OpenQuery() Function.
SELECT CAST(SYS_NC_ROWINFO$ AS XML) SYS_NC_ROWINFO$
FROM OPENQUERY (
Linked_DB_Test,
'SELECT CAST(SYS_NC_ROWINFO$ AS CLOB) SYS_NC_ROWINFO$
FROM TESTUSER.Kevin
WHERE rownum <= 7'
) AS s;
GO
--OLE DB provider "OraOLEDB.Oracle" for linked server "Ted_TSB01_stars3_Interface" returned message "ORA-00932: inconsistent datatypes: expected - got CLOB".
Thanks, Kevin
January 12, 2014 at 12:49 pm
January 13, 2014 at 12:01 pm
Thanks again Jo. However, it is already in xml format on the oracle side.
--Not Working:
--The SYS_NC_ROWINFO$ Column is xmltype with HUGECLOB in the data columns (when looking at the data using TOAD.)
--Here, I am using CAST to cast from xml to clob. Then using CAST to cast from clob to xml using Linked Server and OpenQuery() Function.
SELECT CAST(SYS_NC_ROWINFO$ AS XML) SYS_NC_ROWINFO$
FROM OPENQUERY (
Linked_DB_Test,
'SELECT CAST(SYS_NC_ROWINFO$ AS CLOB) SYS_NC_ROWINFO$
FROM TESTUSER.Kevin
WHERE rownum <= 7'
) AS s;
GO
--OLE DB provider "OraOLEDB.Oracle" for linked server "Ted_TSB01_stars3_Interface" returned message "ORA-00932: inconsistent datatypes: expected - got CLOB".
Thanks, Kevin
January 13, 2014 at 1:48 pm
Got it to Work!
--With Linked Server and OpenQuery() Function.
----Use getClobVal() to retrieve XML data as CLOB value in Oracle Query.
----Use CAST to cast from char to xml in SQL Server Query.
SELECT CAST(SYS_NC_ROWINFO$ AS XML) SYS_NC_ROWINFO$
FROM OPENQUERY (
Linked_DB_Test,
'SELECT x.SYS_NC_ROWINFO$.getclobval() SYS_NC_ROWINFO$
FROM TESTUSER.Kevin x
WHERE rownum <= 7'
) AS s;
GO
BTW, the TESTUSER.Kevin table is an Oracle table that was created from the Oracle XDB.XDB$SCHEMA table for testing purposes because the SYS_NC_ROWINFO$ column contains xml data.
Thanks, Kevin
I hope this helps someone or if you have a better method, please post it.
BTW, the Stars3_Interface.Kevin
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply