Select the product you need help with

You may receive an error message when you try to run distributed queries from a 64-bit SQL Server client to a linked 32-bit SQL Server

Article ID: 906954 - View products that this article applies to.

SYMPTOMS

Consider the following scenario. You define a linked server to a 32-bit Microsoft SQL Server 2000 instance or a SQL Server 7.0 instance such as by using the sp_addlinkedserver stored procedure or a tool that calls this procedure. Then, you try to run distributed queries through that 64-bit SQL Server to the 32-bit SQL Server 2000 or SQL Server 7.0 linked server. In this scenario, you may experience one of the following symptoms:

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 <LinkedServerName> are version <OldVersionNumber>; version <NewVersionNumber> or later is required to ensure proper operation. Please contact your system administrator.
You receive an error message if the following conditions are true:
- 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.
- 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.
The error message is similar to the following:
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 "<LinkedServerName>". The provider supports the interface, but returns a failure code when it is used.

Note If you use the linked SQL Server 2000 server and the server has not been upgraded to SQL Server 2000 SP3 or SQL Server 2000 SP4, you must install SQL Server 2000 SP3 or SQL Server 2000 SP4 first.

Back to the top | Give Feedback

CAUSE

This problem occurs because the system stored procedures are not upgraded as part of the service pack installation. You must manually upgrade the system stored procedures after you install SQL Server 2000 Service Pack (SP3) or SQL Server 2000 Service Pack 4 (SP4) by running instcat.sql.

Back to the top | Give Feedback

RESOLUTION

To resolve this problem, backup the master database then manually run the Instcat.sql script that is included with SQL Server 2000 SP3 or SP4 on the 32-bit SQL Server 2000 server or on the SQL Server 7.0 instance. No restart of SQL Server is required.

Examples of running Instcat.sql by using Osql.exe:

Use Windows Authentication mode

To use Windows Authentication mode to upgrade the system stored procedures on a 32-bit instance of SQL Server 2000 or on the SQL Server 7.0 server, follow these steps:

Log on to the computer by using a Windows account that is a member of the SQL Server sysadmin fixed server role.
Click Run, type cmd.exe, and then click OK.
At the command prompt, type one of the following commands, and then press ENTER:

For a default instance
osql -E -S <LinkedServerName> -i <Location>\instcat.sql
For a named instance
osql -E -S <LinkedServerName>\<InstanceName> -i <Location>\instcat.sql
Note <LinkedServerName>, <InstanceName>, and <Location> represent the linked server name, the instance name, and the full path of the folder that contains the Instcat.sql script. By default, this folder is C:\Program Files\Microsoft SQL Server\MSSQL\Install.

Use SQL Server Authentication mode

To use SQL Server Authentication mode to upgrade the system stored procedures on a 32-bit instance of SQL Server 2000 or on a SQL Server 7.0 server, follow these steps:

Log on to the computer by using any Windows account.
Click Run, type cmd.exe, and then click OK.
At the command prompt, type the following command, and then press ENTER:

For a default instance
osql -U <AdminLogin> -P <AdminPassword> -S <LinkedServerName> -i <Location>\instcat.sql
For a named instance
osql -U <AdminLogin> -P <AdminPassword> -S <LinkedServerName>\<InstanceName> -i <Location>\instcat.sql
Note <AdminLogin> and <AdminPassword> represent the user account that is a member of the SQL Server sysadmin fixed server role.