5

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?

  • 1
    I get the error in 2012 also and always used the workaround. Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64) Oct 20 2015 15:36:27 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) . As per this , it is a known issue because of OPENROWSET limitation. – SqlWorldWide Jul 3 '17 at 14:48
  • which workaround? the same or a better one? – Marcello Miorelli Jul 3 '17 at 14:52
  • 1
    Same one you mentioned. I also use xp_sqlagent_enum_jobs directly especially when doing something based on date and time. – SqlWorldWide Jul 3 '17 at 14:59
  • 1
    I just updated my answer with the query to get the definition. Forgot to paste it in earlier ;-) – Solomon Rutzky Jul 3 '17 at 20:28
5

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 &lt; and &gt; with their respective < and > characters:

USE [msdb];
SELECT OBJECT_DEFINITION(OBJECT_ID(N'msdb.dbo.sp_help_job')) FOR XML PATH('');
  • 1
    +1 nice to meet you OBJECT_DEFINITION thanks srutzky – Marcello Miorelli Jul 3 '17 at 21:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.