Intermittent error in distributed server link, SQL Server 2005 to Oracle
Posted on 2009-05-19
We have an Oracle server linked to an MS SQL 2005 server (running on Windows 2003 Server), and are running distributed queries from the SQL side, e.g.:
select * from (oracle_linked_servername)..table.column
Periodically the connection seems to "break", and the following will be displayed in SSMS:
OLE DB provider "OraOLEDB.Oracle" for linked server "IPOSpire" returned message "Table does not exist.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_INDEXES" for OLE DB provider "OraOLEDB.Oracle" for linked server "IPOSpire". The provider supports the interface, but returns a failure code when it is used.
I've tried to Google the issue, and apparently this error is often due to a security issue - however, I haven't seen anyone else report this as an _intermittent_ error. Why would the connection sometimes work and sometimes fail? Many thanks, and 1,000 points to whoever can solve it!