I have been running a stored procedure that accesses an Oracle database using an OpenQuery string on SQL 2008 R2 (server is MS Windows Server 2008 R2 64-bit running virtualized).  I have 2 seperate databases, Development and Production, that use the same linked server in the same SSMS instance.  The stored procedure has been running successfully on the Development database hourly for a number of weeks.  When I tried to start running the SP on the Production database I received the following error (where F21_YAS is the linked server). 

=======================================================

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "OraOLEDB.Oracle" for linked server "F21_YAS" 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 "F21_YAS".

========================================================

In troubleshooting I found that the same query would fail on the Development database if it was not run as a SP.  When I run the query on my personal computer, with a linked server set up to the same Oracle database, it runs successfully.  I also have other linked servers set up on the Development and Production databases to other Oracle databases that run succesfully. I was not able to see this specific issue in any of my forum searches, any guidance would be appreciated.

My googling prowess led me to these articles.  Try turning on "Allow in process" for the provider as per links 2 and 3

http://blogs.msdn.com/b/dataaccesstechnologies/archive/2011/08/19/how-to-verify-is-an-issue-is-a-linked-server-issue-or-provider-issue.aspx

http://www.sqlservercentral.com/Forums/Topic1298249-1042-1.aspx

http://blogs.msdn.com/b/bindeshv/archive/2008/05/21/demystifying-sql-oracle-distributed-query-issues.aspx

An additional note.  I began migrating my Production SP's to the Development DB and received the same error on another linked server when I tried to activate the SP.  So, in this case the linked server continues to work via SP but will not work if I try to execute manually.  Also, when I test the connection to the Linked server, it is successful.

• Edited by Chemnteach Thursday, June 07, 2012 3:42 PM

Thank you very much Andrew, that fixed it!  I thought I had good Google skills but I didn't get to any of these.  I've been beating my head against the wall on this :-)

We faced same issue in our production server after working on this for more than for hours we came to the conclusion.

Solution for this problem could be below.

Try the test connection.

1.Allow in process for the provider.This could be done by selecting the provider properties and then enabling allow in process.

Run the same query and check if you are facing same error what the client is getting  while you are running it from sql server go ahead with below point.

2.Try running the same query which you are trying to execute in sql server in oracle server.If that is throwing error then its purely oracle or network issue.(The issue might be with data in the oracle server.Involve oracle team and network team to fix the issue)