metadata not defined exception when using OPENROWSET

Question

I am trying to bring data from a stored procedure CTE. Now since my stored procedure uses OPEN EXEC I found that the only way which works is using OPENROWSET. So my intended code looks like below:

;with xyz AS
(
select a.* 
from openrowset('SQLNCLI','Server=(local);Trusted_Connection=yes;',
    'EXEC   [dbo].[usp_GetPayroll]
        @StartDate = "1/1/2016",
        @EndDate =  "4/1/2016",
        @OrganizationID = 183,
AS a
)
select * from xyz

So when I try to do this thing I get below error

Msg 11512, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because the statement 'WITH EnrollmentChanges AS
    (

I did some research and found that when you use the OPENROWSET, it beings back the metadata of only first row. I read something about FMTONLY setting, but it does not work Does someone know any hack around this which works

FYI: Below solution will not work because in that case I will have a dependency on my stored procedure. I do not want to edit this script whenever there is a change in SP. How to get results of stored procedure #1 into a temporary table in stored procedure #2

score 0 · Answer 1 · answered Jul 12 '16 at 15:02

Have you tried to wrap your query with second EXEC(..) statement? As you can read in MSDN:

The EXECUTE statement can be used to send pass-through commands to linked servers. Additionally, the context in which a string or command is executed can be explicitly set.

See the following example:

USE Test1
GO
CREATE PROCEDURE Proc1
AS
BEGIN
    SELECT 'A', 1
    UNION ALL
    SELECT 'B', 2
END
GO
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
Proc1
GO
SELECT * FROM OPENROWSET('SQLNCLI', 'server=.\SQL2014;trusted_connection=yes','EXEC(''EXEC Test1.dbo.Proc1'')')
--OR SELECT * FROM OPENROWSET('SQLNCLI', 'server=.\SQL2014;trusted_connection=yes','EXEC(''Test1.dbo.Proc1'')')

Solution from dev.cdur.pl

metadata not defined exception when using OPENROWSET

1 Answers1