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


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. 

sp_help_job_with_results.sql

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:

    bravo!

  4. Dennis says:

    this works to me.

  5. Ramakant says:

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

  6. JAGADEESH says:

    Good work