October 1, 2008 at 6:54 pm
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
October 1, 2008 at 9:08 pm
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
October 2, 2008 at 2:57 pm
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
October 29, 2010 at 11:39 am
Greg,
I am wondering how you fixed this problem. I am experiencing the same problem.
Thanks so much,
Mike
August 27, 2012 at 1:13 am
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.
August 28, 2012 at 8:55 am
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?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply