Microsoft KB Archive/906954

= 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 or to a linked SQL Server 7.0 server =

Article ID: 906954

Article Last Modified on 11/20/2007

-

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 or a linked SQL Server 7.0 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 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  are version ; version  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 &quot;DBSCHEMA_TABLES_INFO&quot; for OLE DB provider &quot;SQLNCLI&quot; for linked server &quot;&quot;. The provider supports the interface, but returns a failure code when it is used.

</li></ul>

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.

<div class="cause_section">

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

<div class="resolution_section">

RESOLUTION
To resolve this problem, 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 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 or on the SQL Server 7.0 server, follow these steps: <ol> Log on to the computer by using a Windows account that is a member of the SQL Server sysadmin fixed server role.</li> Click Run, type cmd.exe, and then click OK.</li>  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,  , and   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. </li></ol>

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: <ol> Log on to the computer by using any Windows account.</li> Click Run, type cmd.exe, and then click OK.</li>  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  and   represent the user account that is a member of the SQL Server sysadmin fixed server role. </li></ol>

Note After you run the Instcat.sql script, many messages are generated. The last message indicates whether the script ran successfully.

<div class="status_section">

STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section.

Keywords: kbprb kbsql2005connect KB906954

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.