PRB: T-SQL Function OpenQuery Fails to Execute on Linked Servers (297368)

The information in this article applies to:

Microsoft SQL Server 2000 (all editions)

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

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

Modification Type:	Minor	Last Reviewed:	1/28/2004
Keywords:	kbpending kbprb KB297368