Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Linked Server "provider" issues. Expand / Collapse
Author
Message
Posted Thursday, October 29, 2009 1:06 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, September 15, 2014 1:13 AM
Points: 1,535, Visits: 2,577
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.
Post #810536
Posted Monday, February 15, 2010 10:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 1, 2012 10:44 AM
Points: 1, Visits: 42
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
Post #865611
Posted Monday, February 15, 2010 10:26 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:27 AM
Points: 1,552, Visits: 1,750
Have you tried the MS article???

http://support.microsoft.com/kb/251238/
Post #865613
Posted Tuesday, July 6, 2010 4:03 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 11:34 AM
Points: 49, Visits: 175
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.



Post #948224
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse