I have a stored procedure that contains some dynamic SQL. I am trying to execute the stored procedure using OPENROWSET
but am getting the error:
Msg 11514, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement 'EXEC sp_executesql @SQL, N'@row_count_out INT OUTPUT', @row_count_out = @row_count_table OUTPUT;' in procedure 'nachoTest' contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.
My real stored procedure is a bit more complex but this is a working example of the error:
The stored procedure:
CREATE PROCEDURE nachoTest @table_name NVARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @row_count_table INT
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = N'SELECT @row_count_out = COUNT(*) FROM ' + @table_name
EXEC sp_executesql @SQL, N'@row_count_out INT OUTPUT', @row_count_out = @row_count_table OUTPUT;
SELECT @row_count_table AS row_count, @table_name AS table_name
END
GO
Calling it with OPENROWSET
SELECT * FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;', 'EXEC dbo.nachoTest ''e_MFA_All_Tokens''')
I have read a bit online but cannot figure out how to use WITH RESULT SETS
to fix the code. Ideally I'd like to fix the stored procedure but if I have to change the OPENROWSET
call then that is okay too.
Best Answer
The problem is not in your proc but in
OPENROWSET
/OPENQUERY
. Prior to SQL Server 2012 to figure out the resultset metadataset fmtonly
was used. Starting with 2012 it was replaced bysp_describe_first_result_set
that causes this error.here how you can describe your resultset:
More on
EXECUTE WITH RESULT SETS
here: SQL Server 2012 T-SQL at a Glance – EXECUTE WITH RESULT SETS