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 to Oracle Expand / Collapse
Author
Message
Posted Wednesday, October 01, 2008 6:54 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 1:44 PM
Points: 700, Visits: 785
For anyone who can help. I receive the following error when running a query.

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server " " reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "OraOLEDB.Oracle" for linked server " ".

QUERY:
SELECT
i.item_no, -- Character Field
x.ref_item_no -- Character Field
FROM
.TABLEA i
INNER JOIN .TABLEB X
ON I.EDP_NO = X.EDP_NO
WHERE x.status='U'

When running the query, I begin to see Management Studio return results. Then, for some unknown reason, it fails with the above error. I have searched around on the error, but not much on Google, Microsoft, or Oracle. It is interesting, sometimes the query returns 9K rows, sometimes 20K, and sometimes 50K, or by chance just a few hundred. The results are random each time before the failure.

I have other queries running that work fine without issue. This ONE query seems to be causing me problems.

ENVIRONMENT:
Windows 2003 R2 x64
SQL Server 2005 SP2
Oracle Client 10.2.0.2.20
Windows Clustering (Active/Passive)

DataCenter of SQL Server: AZ
DataCenter of Linked Server (Oracle): Oregon

I have set the "Allow in Process" for the Oracle Provider, with no luck. Keep in mind, other queries using the same Linked Server works perfectly fine. Some take longer and return more data, but the other queries return successfully.

Have any of you ran into this issue? I am tempted to reinstall the Oracle clients with the latest OLEDB drivers to see if we can get it working.

Please help if you have seen this before. We are supposed to go live with some reports that call stored procedures which use a single Oracle Linked Server this weekend.

I forgot to mention, i have tried both 4-part names or OpenQuery with no luck.

Thanks
Greg



Post #579447
Posted Wednesday, October 01, 2008 9:08 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 3:20 PM
Points: 2,105, Visits: 3,560
Can't say that I have seen it but I wonder what you would see if you were to run a trace on the Oracle side for that spid. Wondering if the connection is being closed or if you can trap the error on the Oracle side.

David

@SQLTentmaker
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #579463
Posted Thursday, October 02, 2008 2:57 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 1:44 PM
Points: 700, Visits: 785
There are no errors on the Oracle side. The SQL being sent to Oracle completes successfully. Each time, the OLEDB will error as mentioned, but on the Oracle side the query sent using OPENQUERY will continue till completion.

This is a weird intermittent issue. We are upgarding the Oracle clients to see if it fixes the issue. I will let everyone know if the issue is resolved by either a Patch upgrade or a complete new install of the 11G client.

G



Post #579961
Posted Friday, October 29, 2010 11:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 1:03 PM
Points: 3, Visits: 253
Greg,

I am wondering how you fixed this problem. I am experiencing the same problem.

Thanks so much,

Mike
Post #1013246
Posted Monday, August 27, 2012 1:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 14, 2013 7:34 AM
Points: 1, Visits: 19
Has anybody got a solution for this?
I expericence the exact same problem. Strange thing is, that i have 2 views to query through the oracle linked server - one of them works fine but when querying the other one, I experince the problem as described above. Only difference is that we are running SQL Server 2008.

I would really appreciate if someone could help me on this one.
Post #1350224
Posted Tuesday, August 28, 2012 8:55 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, April 17, 2014 2:32 PM
Points: 535, Visits: 1,630
Oops, just noticed the date, never mind.

How many rows are returned without the WHERE clause?

I may be wrong, but I think the openquery will filter the results in Oracle, whereas the query in your post wants to move all the rows over to sql, then filter on the WHERE clause.

Maybe there is a record containing incompatible data that is filtered out by the where clause. The openquery doesn't attempt to return it, but the normal query is trying to bring it across. Can you select all the data from both tables individually using the linked server?
Post #1351010
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse