Distributed query via linked server fails to execute from a 64-bit SQL Server 2005 client to a linked 32-bit SQL Server 2000 server or to a linked SQL Server 7.0 server

You may experience error message while running a distributed query from SQL 2005 64 bit client instance to SQL 2000 or lower version’s 32 bit server instance.

Possible error message varies on the version of 32 bit server as below:

If the 32-bit SQL Server 2000 server has not been upgraded to SQL Server 2000 Service Pack 3 (SP3) or SQL Server 2000 Service Pack 4 (SP4), you receive the following error message:

The ODBC catalog stored procedures installed on server are version ; version or later is required to ensure proper operation. Please contact your system administrator.

You receive following error message if the following conditions are true:
1. SQL Server 2000 SP3 or SQL Server 2000 SP4 is installed on the 32-bit SQL Server 2000 server, or you use the linked SQL Server 7.0 server.
2. The versions of the system stored procedures on the 32-bit SQL Server 2000 server or on the SQL Server 7.0 server are different from the service pack version that is installed on the server.

OLE DB provider "SQLNCLI" for linked server "" returned message "Unspecified error".

OLE DB provider "SQLNCLI" for linked server "" returned message "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.

However both error messages are generated because systems catalogs of 32 bit server are not compatible to support a query sent from higher versions. So to resolve the issue, systems catalog must be updated.

Please follow below step to resolve this issue:

Step 1: If you are using SQL 2000, ensure that it is upgraded to SP4 (at least up to SP3 which is not recommended)

Step 2: Manually execute the Instcat.sql script that is included with SQL Server 2000 SP4 on the 32-bit SQL Server 2000 server or on the SQL Server 7.0 server

Follow below steps to manually execute Instcat.sql:

If you are using Windows authentication:

1. Log on to the 32 bit server by using a Windows account that is a member of the SQL Serversysadmin fixed server role.
   
2. At the command prompt, type one of the following commands, and then press ENTER: For a default instance

osql -E -S  -i \instcat.sql

For a named instance

osql -E -S \ -i \instcat.sql

 If you are using SQL Authentication:

1. Log on to the computer by using any Windows account.
2. At the command prompt, type the following command, and then press ENTER: For a default instance

osql -U  -P  -S  -i \instcat.sql

 For a named instance

 osql -U  -P  -S \ -i \instcat.sql

Note: Reference taken from http://support.microsoft.com/kb/906954/en-us. Please refer to this link for more details.