1

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.

4

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

  • I see. And there is no way to modify the stored procedure to provide that data somehow? – IMTheNachoMan Dec 18 '17 at 16:03
  • Because I intend to use this to INSERT INTO #temptable SELECT * FROM OPENROWSET.... Right now I am using INSERT INTO #temptable EXEC ... but with that I have to create #temptable beforehand. I want to use INSERT INTO so I don't have to create the table before hand but if OPENROWSET requires the table schema then it is the same as just creating the table before hand. – IMTheNachoMan Dec 18 '17 at 16:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.