Issue:In SQL 2012, stored procedures that call any other stored procedures or
extended stored procedures that return result sets should specify WITH RESULT SETSSQL 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 linkWhen 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 1The 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.
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 ?
this works to me.