Microsoft KB Archive/936223

= FIX: Error messages when you run a query against a linked server that you create in SQL Server 2005: &quot;Statement(s) could not be prepared&quot; and &quot;The column prefix '' does not match with a table name or alias name used in the query&quot; =

Article ID: 936223

Article Last Modified on 6/27/2007

-

APPLIES TO


 * Microsoft SQL Server 2005 Developer Edition
 * Microsoft SQL Server 2005 Enterprise Edition
 * Microsoft SQL Server 2005 Standard Edition
 * Microsoft SQL Server 2005 Standard X64 Edition
 * Microsoft SQL Server 2005 Standard Edition for Itanium-based Systems
 * Microsoft SQL Server 2005 Enterprise X64 Edition
 * Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems

-



Bug #: 50001186 (SQL Hotfix)



SYMPTOMS
Consider the following scenario. In Microsoft SQL Server 2005, you create a linked server by using the SQL Native Client OLE DB provider (SQLNCLI). The linked server is linked to a server that has Microsoft SQL Server 2000 installed. In this scenario, you receive the following error messages when you run a query against the linked server:

Error message 1

Msg 8180, Level 16, State 1, Line 1

Statement(s) could not be prepared.

Error message 2

Msg 107, Level 16, State 2, Line 1

The column prefix ' ' does not match with a table name or alias name used in the query.



CAUSE
This problem occurs because SQL Server 2005 generates an execution plan that has a remote query. SQL Server 2005 must execute the remote query against SQL Server 2000 to retrieve the required data. SQL Server 2000 cannot handle the remote query. Therefore, error 107 occurs in SQL Server 2000. Then, error 107 is propagated back to SQL Server 2005. Therefore, error 107 occurs in SQL Server 2005, and error 8180 occurs in SQL Server 2005.



Cumulative update information
To resolve this problem, obtain the cumulative update package (build 3175) for SQL Server 2005 Service Pack 2 (SP2). For more information, click the following article number to view the article in the Microsoft Knowledge Base:

936305 Cumulative update package 2 for SQL Server 2005 Service Pack 2 is available



WORKAROUND
To work around this problem, use Microsoft OLE DB Provider for ODBC to create the linked server against SQL Server 2000.



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



MORE INFORMATION
The cumulative update package (build 3175) for SQL Server 2005 SP2 introduces trace flag 4112. After you apply the cumulative update package (build 3175) for SQL Server 2005 SP2, you must enable trace flag 4112. For information about how to enable a trace flag in SQL Server 2005, see the &quot;Remarks&quot; section of the following Microsoft Developer Network (MSDN) Web site:

http://msdn2.microsoft.com/en-us/library/ms188396.aspx

Steps to reproduce the problem
 Start SQL Server Management Studio, and then connect to an instance of SQL Server 2005.  Create a new query, and then run the following statements. EXEC sp_addlinkedserver @server = '', @provider = 'SQLNCLI', @srvproduct = '', @datasrc = '', @catalog = ' is a placeholder for the name of the linked server.  is a placeholder for the instance name for the linked server.</li>  is a placeholder for the database name.</li></ul> </li></ol>

After you run these statements, you receive the error messages that are mentioned in the &quot;Symptoms&quot; section.

Keywords: kbsql2005engine kbexpertiseadvanced kbfix kbpubtypekc kbqfe KB936223

-

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

© Microsoft Corporation. All rights reserved.