Error Msg 7355 While using OPENROWSET in SQL Server 2005 SP1

I run the following statement:

SELECT a.*
FROM OPENROWSET
('SQLNCLI', 'Server=MyServer;Trusted_Connection=yes;',
'exec distribution..sp_replmonitorhelpsubscription
@.publisher = MyServer,
@.publication_type = 0,
@.mode = 1') AS a;

and I get the following error:

Msg 7355, Level 16, State 1, Line 2
The OLE DB provider "SQLNCLI" for linked server "(null)" supplied
inconsistent metadata for a column. The name was changed at execution time.

My research tells me that this issue was resovled in SQL 2000 SP2. What am I
doing wrong?

This error happens if the result set metadata depends on the parameters and/or can be different at execution time. This is typically the case for results that are constucted dynamically and if columns can change between executions or for different parameter invocations. The OPENROWSET, OPENQUERY calls determine the shape and metadata of the result set by using SET FMTONLY ON setting (this is for SQL Server data sources). With this setting ON, the server will execute the SP but it will not generate results or execute all branches of if..else logic or initialize variables etc. So it is possible that the result set is generated with defaults and contains different set of columns/names. Then when the actual execution of the SP happens, the result set could be different and this will throw the error.

Below are the reasons why you will get the error:

1. If different names were returned for a particular column (based on the ordinal position in the result set)

2. If extra column(s) were returned at execution time than what was determined using SET FMTONLY ON

3. Metadata of the column was different (float changed to decimal or int to bigint and so on)

To resolve the error, you should either correct the SP or use a wrapper SP that can handle the different result sets and provide consistent output. Optionally, you can execute the SP using 4-part names directly or use the EXECUTE AT (new in SQL Server 2005). Both these modifications will will require a linked server setup.