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


Linked Server to Oracle


Linked Server to Oracle

Author
Message
Greg Grow
Greg Grow
Say Hey Kid
Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)

Group: General Forum Members
Points: 705 Visits: 957
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



David Benoit
David Benoit
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2108 Visits: 3650
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

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Greg Grow
Greg Grow
Say Hey Kid
Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)

Group: General Forum Members
Points: 705 Visits: 957
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



Mike Zerwig
Mike Zerwig
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 313
Greg,

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

Thanks so much,

Mike
hmk 83564
hmk 83564
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: 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.
Ed B
Ed B
Mr or Mrs. 500
Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)

Group: General Forum Members
Points: 594 Visits: 1775
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?
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