You may receive an error message when you try to run distributed queries from a 64-bit SQL Server 2005 client to a linked 32-bit SQL Server 2000 server (906954)


The information in this article applies to:

  • Microsoft SQL Server 2005 Standard X64 Edition
  • Microsoft SQL Server 2005 Developer Edition

SYMPTOMS

Consider the following scenario. You define a linked 32-bit Microsoft SQL Server 2000 server by using the sp_addlinkedserver stored procedure. Then you try to run distributed queries from a 64-bit SQL Server 2005 client to the linked 32-bit SQL Server 2000 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.
    • The versions of the system stored procedures on the 32-bit SQL Server 2000 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 This problem also occurs if you access linked 32-bit SQL Server 2000 Desktop Engine (MSDE) servers from the 64-bit SQL Server 2005 client.

CAUSE

This problem occurs because the system stored procedures were not upgraded to SQL Server 2000 SP3 or SQL Server 2000 SP4. You must manually upgrade the system stored procedures after you install SQL Server 2000 SP3 or SQL Server 2000 SP4.

RESOLUTION

To resolve this problem, install SQL Server 2000 SP3 or SQL Server 2000 SP4 on the 32-bit SQL Server 2000 server. 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.

Use Windows Authentication mode

To use Windows Authentication mode to upgrade the system stored procedures on a 32-bit instance of SQL Server 2000, follow these steps:
  1. Log on to the computer by using a Windows account that is a member of the SQL Server sysadmin fixed server role.
  2. Click Run, type cmd.exe, and then click OK.
  3. 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, follow these steps:
  1. Log on to the computer by using any Windows account.
  2. Click Run, type cmd.exe, and then click OK.
  3. 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.
Note After you run the Instcat.sql script, many messages are generated. The last message indicates whether the script ran successfully.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
Modification Type:MajorLast Reviewed:5/8/2006
Keywords:KBsql2005connect kbprb KB906954 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.