Microsoft KB Archive/297368

= T-SQL function OpenQuery fails to execute on linked servers =

Article ID: 297368

Article Last Modified on 3/30/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q297368



SYMPTOMS
If you attempt to execute an OpenQuery command against a SQL Server 2000 linked server, no results will be returned. For example, executing the following query: SELECT * FROM OPENQUERY (ServerName, 'xp_logininfo') generates the following error:

Server: Msg 7357, Level 16, State 2, Line 1

Could not process object 'xp_logininfo'. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.



CAUSE
OpenQuery needs metadata about the columns at compile time. To retrieve the metadata, OpenQuery makes OLE DB calls which eventually execute the query with the SET FMTONLY ON option. As a result, OpenQuery fails to execute. Because of this behavior, queries that do not return metadata about columns, such as SQL Server stored procedures, cannot be run by using OpenQuery.



WORKAROUND
To execute stored procedures, you can use four-part name distributed queries. For example: exec ServerName.master.dbo.xp_logininfo Alternatively, you can use the SET FMTONLY OFF option before the statement to run the extended stored procedure. For example: SELECT * FROM OPENQUERY ('ServerName','SET FMTONLY OFF EXEC xp_logininfo')



MORE INFORMATION
By design, OpenQuery (T-SQL) cannot be used to run extended stored procedures on a linked server. The following sample code demonstrates this behavior: Use Master GO exec sp_addlinkedserver 'ServerName' GO exec sp_addlinkedsrvlogin 'ServerName', false, 'sa', 'sa', 'SaPassword' GO SELECT * FROM OPENQUERY (ServerName, 'xp_logininfo') GO

Keywords: kbpending kbprb KB297368

-

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

© Microsoft Corporation. All rights reserved.