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

Question

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              = NULL,
  @date_comparator            CHAR(1)          = NULL,
  @date_created               DATETIME         = NULL,
  @date_last_modified         DATETIME         = NULL,
  @description                NVARCHAR(512)    = NULL

        EXEC msdb.dbo.sp_help_job @job_id,  
            @job_name,
            @job_aspect,
            @job_type,
            @owner_login_name,
            @subsystem,
            @category_name,
            @enabled,
            @execution_status,
            @date_comparator,
            @date_created,
            @date_last_modified,
            @description
            WITH RESULT SETS
            ( 
             (
                job_id                      UNIQUEIDENTIFIER, 
                originating_server          NVARCHAR(30), 
                name                        SYSNAME, 
                [enabled]                   TINYINT, 
                [description]               NVARCHAR(512), 
                start_step_id               INT, 
                category                    SYSNAME, 
                [owner]                     SYSNAME, 
                notify_level_eventlog       INT, 
                notify_level_email          INT, 
                notify_level_netsend        INT, 
                notify_level_page           INT, 
                notify_email_operator       SYSNAME, 
                notify_netsend_operator     SYSNAME, 
                notify_page_operator        SYSNAME, 
                delete_level                INT, 
                date_created                DATETIME, 
                date_modified               DATETIME, 
                version_number              INT, 
                last_run_date               INT, 
                last_run_time               INT, 
                last_run_outcome            INT, 
                next_run_date               INT, 
                next_run_time               INT, 
                next_run_schedule_id        INT, 
                current_execution_status    INT, 
                current_execution_step      SYSNAME, 
                current_retry_attempt       INT, 
                has_step                    INT, 
                has_schedule                INT, 
                has_target                  INT, 
                [type]                      INT 
             )
             )
')

Questions:

what was the change(s) in sql server 2016 that triggered this?

Solomon Rutzky · Accepted Answer · 2017-07-03T20:27:54.793

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 < and > characters:

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

score 0 · Answer 2 · answered Jan 15 '21 at 16:29

There's no change in OPENROWSET functionality between 2012 and 2016.

This seems like a settings issue, In 2012 you (or someone) has probably set the service broker to enabled, configured db_sendmail with recipients, added permissions for metadata discovery for your account, etc.

But in 2016 since it was a new install maybe you don't have one or all of those settings configured. The "WITH RESULT SETS" solution you've found is brilliant however, and removes the need to go troubleshooting and potentially open up settings and configuration that make your DB overall less secure.

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

2 Answers2

Linked