Code Project

Link Unit

Monday, October 08, 2012

Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider


Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "dbAmp.dbAmp" for linked server  "XYZ". The provider supports the interface, but returns a failure code when it is used.

Source:
http://www.sqlservercentral.com/Forums/Topic491682-149-1.aspx

Troubleshooting depends on how linked server is used i.e by using OPENQUERY or by four part name.
If openquery works but four part name queries do not, it could be that the ODBC driver is running out of memory.

Solution:
In SQL server enterprise manager go to "Server objects" - "Linked servers" - "providers". Then enable "Allow inprocess"

Hope it helps.

No comments: