Microsoft SQL Server Front End Blog

The writers of this blog are a part of the Microsoft SQL Server Manageability team. We will be blogging about our tools, scripts, webcasts, and miscellaneous tips that will help you get the most out of SQL Agent, Data collector and other tools

Workaround: SQL Server 2012 - OPENROWSET on sp_help_job throws "The metadata could not be determined"

Workaround: SQL Server 2012 - OPENROWSET on sp_help_job throws "The metadata could not be determined"

  • Comments 2
Issue:
In SQL 2012, stored procedures that call any other stored procedures or 
extended stored procedures that return result sets should specify WITH RESULT SETS

SQL Agent's stored procedure sp_help_job 
- returns single resultset if no parameters are passed to stored procedure. 
- returns 3 resultsets if job_id or job_name is specified. MSDN link

When RESULTSET is not described, Openrowset on msdb.dbo.sp_help_job throws following error
Msg 11520, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement 'EXECUTE master.dbo.xp_sqlagent_is_starting @retval OUTPUT' in procedure 'sp_is_sqlagent_starting' invokes an extended stored procedure.


Workaround:
Create a wrapper stored procedure that calls sp_help_job WITH RESULT SET() 
and use the wrapper stored procedure in OPENROWSET()

Sample Code:
T-SQL script can downloaded from here





Known issues:
1) OPENROWSET returns only the first rowset.
2) sp_describe_first_result_set returns only metadata for first resultset. 

If you have any issues, please report to SQL Server team. 
Attachment: sp_help_job_with_results.sql
Leave a Comment
  • Please add 4 and 3 and type the answer here:
  • Post
  • great job

  • I have also issue with this.

    You are only right when the result set is known.

    If the procedure result set is dynamic crosstab/Pivot type then result is unknown and in that condition how can be same achieved ?

    Will u please advise ?

Page 1 of 1 (2 items)