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

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.

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. 


Comments (7)

  1. Dinesh Dattatray Vishe says:

    great job

  2. Sanjeeb Kumar Chaudhary says:

    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 ?

  3. Hebert Dorigon says:


  4. Dennis says:

    this works to me.

  5. Ramakant says:

    Its works and Useful script. Thank, my issue solved.

  6. JAGADEESH says:

    Good work

Skip to main content