7. června 2012 15:09
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.
7. června 2012 15:17
My googling prowess led me to these articles. Try turning on "Allow in process" for the provider as per links 2 and 3
SQL Server DBA
Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you
- Označen jako odpověď Chemnteach 7. června 2012 20:12
7. června 2012 15:31
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.
- Upravený Chemnteach 7. června 2012 15:42
7. června 2012 20:14Thank 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 :-)