The metadata could not be determined because statement invokes an extended stored procedure

in sql server 2012 I used to have a look at the jobs by getting the output of SP_HELP_JOB

-- https://www.sqlservercentral.com/Forums/Topic259078-8-1.aspx
-- getting data from sp_help_job into a temp table
-- marcelo miorelli
-- 01-april-2013

IF OBJECT_ID('TEMPDB..#JOBiNFO') IS NOT NULL
   DROP TABLE #JobInfo

IF OBJECT_ID('TEMPDB..#ScheduleInfo') IS NOT NULL
   DROP TABLE #ScheduleInfo

SELECT * INTO #JobInfo
FROM OPENROWSET('sqloledb', 'server=(local);trusted_connection=yes'
, 'set fmtonly off exec msdb.dbo.sp_help_job -- @execution_status=4')

SELECT * INTO #ScheduleInfo
FROM OPENROWSET('sqloledb', 'server=(local);trusted_connection=yes'
, 'set fmtonly off  exec msdb.dbo.sp_help_schedule')

Now on sql server 2016 this is not working.

I am getting this error message:


Msg 11520, Level 16, State 1, Procedure sp_describe_first_result_set,
Line 1 [Batch Start Line 9] 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.

when looking for how to get the metadata of a stored procedure,

it says


SET FMTONLY ON can be used to return just column information, no rows
will be processed or returned.

The way that is working for me is this one:

```
IF OBJECT_ID('TEMPDB..#JOBiNFO') IS NOT NULL
DROP TABLE #JobInfo

SELECT * INTO #JobInfo
FROM OPENROWSET('sqloledb', 'server=(local);trusted_connection=yes'
, 'set fmtonly off;

DECLARE
@job_id UNIQUEIDENTIFIER = NULL,
@job_name SYSNAME = NULL,
@job_aspect VARCHAR(9) = NULL,
@job_type VARCHAR(12) = NULL,
@owner_login_name SYSNAME = NULL,
@subsystem NVARCHAR(40) = NULL,
@category_name SYSNAME = NULL,
@enabled TINYINT = NULL,
@execution_status INT

Nothing (that I can see) changed between SQL Server 2012 and 2016 with respect to this issue. Not working with temp tables, multiple result sets, extended stored procedures, CLR objects, etc have all been limitations of sp_describe_first_result_set since it was introduced in SQL Server 2012. So, I am thinking that you actually had this running on a version prior to SQL Server 2012 where it would have worked.

That being said, while you did find a work around via WITH RESULT SETS, I would still highly recommend that you take a look at the definition of those two system stored procedures and copy the subsection of code that you are actually using into a stored procedure that does the operation directly. This way you won't need the multiple extra layers of abstraction that you are currently using (e.g. OPENROWSET, etc).

Try the following to get the definition. Copy and paste the resulting XML into the text editor and replace < and > with their respective `` characters:

USE [msdb];
SELECT OBJECT_DEFINITION(OBJECT_ID(N'msdb.dbo.sp_help_job')) FOR XML PATH('');

StackExchange Database Administrators Q#177861, answer score: 6