Sql-server – metadata could not be determined because stored procedure contains dynamic SQL

dynamic-sqlopenrowsetsql serversql server 2014stored-procedures

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 metadata set fmtonly was used. Starting with 2012 it was replaced by sp_describe_first_result_set that causes this error.

here how you can describe your resultset:

SELECT * FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;', 'EXEC dbo.nachoTest ''e_MFA_All_Tokens'' WITH RESULT SETS
(
  (
    cnt int, name sysname
  )
)')

More on EXECUTE WITH RESULT SETS here: SQL Server 2012 T-SQL at a Glance – EXECUTE WITH RESULT SETS