DEVELOPMENT: Cannot obtain the schema rowset “DBSCHEMA_TABLES_INFO” for OLE DB provider “SQLNCLI” for linked server

Running a query from SQL Server 2008 over a linked server connection to a SQL Server 2000 server causes the following error:

OLE DB provider “SQLNCLI10″ for linked server “mylinkedserver” returned message “Unspecified error”.
OLE DB provider “SQLNCLI10″ for linked server “mylinkedserver” returned message “The stored procedure required to complete this operation could notbe found on the server. Please contact your system administrator.”.
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset “DBSCHEMA_TABLES_INFO” for OLE DB provider interface, but returns a failure code when it is used.

Or

The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset “DBSCHEMA_TABLES_INFO” for OLE DB provider “SQLNCLI” for linked server “”. The provider supports the interface, but returns a failure code when it is used.

Firstly, SQL Server 2000 SP4 (service pack 4) must be installed.

Then the system stored procedures must be manually upgraded.

Note that when manually upgrading the system stored procedures, we used SQL Server Authentication mode which requires the syntax:
osql -U [adminlogin]-P [adminpassword]-S [linkedservername]-i [location]instcat.sql

We first entered the line as:
osql -U sa -P myPassword -S myServer -i C:Program FilesMicrosoft SQL ServerMSSQLInstallinstcat.sql

But this just bought up the osql help /? list of parameters. To avoid this, make sure that the -i path is in speech marks!
osql -U sa -P myPassword -S myServer -i “C:Program FilesMicrosoft SQL ServerMSSQLInstallinstcat.sql“

Note that the manual upgrade takes a couple of minutes to run and there are a whole bunch of message and numbers displayed in the command window as it executes. If the process is successful, then the last but one line will read “instcat.sql completed successfully”.

In our experience, the SQL Service did not need to be restarted, the fix worked immediately and we were able to query the SQL Server 2000 server from SQL Server 2008 over the link immediately:
SELECT COUNT(*)
FROM myLinkedServer.myDatabase.dbo.myTable

We hope that this tip helps others new to SQL Server 2008 development!

Please see our website for more information on our software development services.