October 29, 2009 at 1:06 am
Dear Experts,
We have an application which pulls data from Oracle and dump in SQL Server and vice versa.
It also does all the insert/update/delete operations as well.
We created a linked server to oracle and using in stored procedures for querying oracle DB.
But we are facing the following issues
When we add linked server using the provider "MSDAORA.1", we get following error for certain queries.
The OLE DB provider "MSDAORA" for linked server "LinkedServerName" supplied inconsistent metadata for a column. The column "columnname" (compile-time ordinal 1) of object "TableName" was reported to have a "DBTYPE" of 130 at compile time and 5 at run time.
When we add linked server using the provider "OraOLEDB.Oracle.1", we get following error for certain queries.
Msg 9803, Level 16, State 1, Line 1. Invalid data for type "numeric".
What's the workaround for these issues..? Do I need to add 2 linked servers with 2 diff providers and use accordingly.?Is there any other single solution..?
We have SQL Server 2005 32 bit version. With SP2. Please help...It's urgent.
Thanks in advance.
February 15, 2010 at 10:19 am
I had the same issue. I created a view which pulled data from an Oracle DB via a linked server. My view worked fine.
When I referenced the view in another database, I got the error: The OLE DB provider "OraOLEDB.Oracle" for linked server ... supplied inconsistent metadata for a column.
I came to find that my compatibility level for the database containing the view was set to 100 - SQL Server 2008, but the database with a query referencing the view was set to 80 - SQL Sever 2000. When I changed my compatibility level to 100, I stopped receiving the error.
Hope this helps.
Mitch Owen
February 15, 2010 at 10:26 am
Have you tried the MS article???
July 6, 2010 at 4:03 pm
Since you are using sql2k5, not 2k8, changing the version to 100 would seem unlikely to fix your problem.
I had a similar problem with links to Oracle 9 & 10 using the Oracle OLEDB provider from SQLServer 2005 SP2.
I don't recall if I had the exact error msgs you report but it sounds similar.
A call to MS Premium support eventually disclosed that the problem was with that provider. We found that numeric values that ended in '0' would confuse the provider. It was reading that value as the number of decimal places in the column (0:= integer). So, the read would proceed properly until the first value ending in zero. Then it would fail.
I ended up using openquery() to read all data with inline conversions all numerics to character types before inserting the rows to a local SQL Server table. Then, I altered the local columns to appropriate types. Yes, this was a major pain but worked.
If you would like more information on this, let me know.
Some have reported better luck with the MS provider for Oracle 8 used against later versions. This seems to be workable where no later vintage data types are involved (requiring a later version of the Oracle provider). Seemingly, Oracle stopped providing MS with information necessary to update their provider so they froze on version 8. This may be an urban myth.
SSIS does not seem to have this problem - Connectors are not Providers. The recently released connector for Oracle and Teradata by Attunity (check MS download) seems to perform very well but does require Sql 2k8.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply