包含结果集的sp_help_jobhistory错误

Question

sql server 2012

我有一个包装sp_help_jobhistory输出的过程。存储过程执行时没有问题，但在执行以下命令时返回错误：

SET FMTONLY OFF
EXEC msdb.dbo.sp_describe_first_result_set @tsql = N'exec msdb.dbo.sp_help_jobhistory_with_results'
GO

这是我得到的错误：

Msg 11512, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1 [Batch Start Line 3]
The metadata could not be determined because the statement 'EXEC msdb.dbo.sp_help_jobhistory 
        @job_id
        ,@job_name
        ,@step_id
        ,@sql_message_id
        ,@sql' in procedure 'sp_help_jobhistory_with_results' is not compatible with the statement 'EXEC msdb.dbo.sp_help_jobhistory 
        @job_id
        ,@job_name
        ,@step_id
        ,@sql_message_id
        ,@sql' in procedure 'sp_help_jobhistory_with_results'.

我做错了什么？以下是代码

use msdb
go


create proc [dbo].[sp_help_jobhistory_with_results]
@job_id                 uniqueidentifier=null
,@job_name              sysname=null
,@step_id               int=null
,@sql_message_id        int=null
,@sql_severity          int=null
,@start_run_date        int=null
,@end_run_date          int=null
,@start_run_time        int=null
,@end_run_time          int=null
,@minimum_run_duration  int=null
,@run_status            int=null
,@minimum_retries       int=null
,@oldest_first          int=0
,@server                nvarchar(30)=null
,@mode                  varchar(7)='SUMMARY'
AS
BEGIN
    IF (@mode <>'SUMMARY' and (@job_name is not null or @step_id is not null))
    BEGIN
        -- returns 17 columns
        EXEC msdb.dbo.sp_help_jobhistory 
        @job_id
        ,@job_name
        ,@step_id
        ,@sql_message_id
        ,@sql_severity
        ,@start_run_date
        ,@end_run_date
        ,@start_run_time
        ,@end_run_time
        ,@minimum_run_duration
        ,@run_status
        ,@minimum_retries
        ,@oldest_first
        ,@server
        ,@mode
        WITH RESULT SETS
        ( 
            (
            instance_id         int
            ,job_id             uniqueidentifier
            ,job_name           sysname
            ,step_id            int
            ,step_name          sysname
            ,sql_message_id     int
            ,sql_severity       int
            ,[message]          nvarchar(1024)
            ,run_status         int
            ,run_date           int
            ,run_time           int
            ,run_duration       int
            ,operator_emailed   nvarchar(20)
            ,operator_netsent   nvarchar(20)
            ,operator_paged     nvarchar(20)
            ,retries_attempted  int
            ,[server]           nvarchar(30)
            )
        )
    END
    -- returns 11 columns
    ELSE
    BEGIN
        EXEC msdb.dbo.sp_help_jobhistory 
        @job_id
        ,@job_name
        ,@step_id
        ,@sql_message_id
        ,@sql_severity
        ,@start_run_date
        ,@end_run_date
        ,@start_run_time
        ,@end_run_time
        ,@minimum_run_duration
        ,@run_status
        ,@minimum_retries
        ,@oldest_first
        ,@server
        ,@mode
        WITH RESULT SETS
        ( 
            (
            job_id              uniqueidentifier
            ,job_name           sysname
            ,run_status         int
            ,run_date           int
            ,run_time           int
            ,run_duration       int
            ,operator_emailed   nvarchar(20)
            ,operator_netsent   nvarchar(20)
            ,operator_paged     nvarchar(20)
            ,retries_attempted  int
            ,[server]           nvarchar(30)
            )
        )
    END 
END

Answer 1

不使用WITH RESULT SETS就无法确定来自sp_help_jobhistory_with_results的METADATA，就像您在main过程中所做的那样。改用这个(你必须填写这些列)

EXEC msdb.dbo.sp_describe_first_result_set @tsql = N'exec msdb.dbo.sp_help_jobhistory_with_results with result sets ((Col1 datatype, Col2 datatype, etc....))'

Answer 2

Raises an error if the Database Engine cannot determine the metadata for the first query that will be executed by performing a static analysis. https://msdn.microsoft.com/en-us/library/ff878602.aspx的系统存储过程

我以前没有使用过这个存储过程，所以我不知道为什么需要运行它。我没有看到任何证据表明它是针对存储过程运行的。http://stevestedman.com/2013/04/t-sql-2012-procedure-sp_describe_first_result_set/