July 20, 2005 at 8:37 am
I have added a linked server to Oracle (Think version is 8i) using
EXEC sp_addlinkedserver
@server = 'ORA_EUCLID',
@srvproduct = 'Oracle',
@provider = 'MSDAORA',
@datasrc = 'EUCLID'
It links ok and I can view all the tables and views, once I use openquery however and do a simple select from the table I get
Server: Msg 7320, Level 16, State 2, Line 1
Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandText::Execute returned 0x80040155].
No idea what this means..
July 20, 2005 at 8:53 am
Check out this reference:
July 20, 2005 at 9:10 am
...and this one
http://support.microsoft.com/default.aspx?scid=kb;en-us;280106
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
November 27, 2007 at 9:14 am
I recently ran across this myself. We had multiple linked servers on different SQL servers hitting Oracle and doing so quite well for years. Then it was brought to my attention one was failing. When checking it out on multiple other servers it was discovered that none of the Oracle linked servers were working. Since neither Oracle or SQL had patches released during that period, and no changes were made to the linked server or queries supported by the linked servers it had to be a rolled-out patch. Though I can not identify the specific patch that caused this I can tell you that the original symptom was that I was not even able to expand the linked servers in the SSMS or Enterprise Manager and view objects. Re-installing the OLEDB drivers from the Oracle client let me do that, so long as I changed the provider of the linked server from the Oracle Client in the ora home to the Oracle OLEDB provider. However, the queries still failed. They would return the schema, but no rows and would fail with the "Could not execute query against OLE DB provider" "OraOLEDB.Oracle" error.
The final resolution was to change the provider in the linked server to the Microsoft OLEDB Provider for Oracle and then re-install the client connectivity objects from the SQL DVD. Worked like a charm from there.
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply