Linked Server to Oracle

  • 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

  • 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

  • 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

  • Greg,

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

    Thanks so much,

    Mike

  • 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.

  • 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