2

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.

Community
  • 1
IMTheNachoMan
  • 155
  • 1
  • 2
  • 7

1 Answers1

6

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

sepupic
  • 11,267
  • 18
  • 27