Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Linked Server "provider" issues.


Linked Server "provider" issues.

Author
Message
Joy Smith San
Joy Smith San
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2398 Visits: 3200
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.
Mitch Owen
Mitch Owen
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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
sql_lock
sql_lock
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1858 Visits: 1934
Have you tried the MS article???

http://support.microsoft.com/kb/251238/
Scott MacCready
Scott MacCready
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 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.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search