TRY/CATCH 对 SQL Server 代理错误不起作用? [英] TRY/CATCH does not work on SQL Server Agent error?

问题描述

我使用 sp_start_job 开始工作.

作业(test2)只有一步:

select getdate()
waitfor delay '00:00:10'

TRY/CATCH 代码:

begin try
    EXEC msdb.dbo.sp_start_job @job_name = 'test2'
end try
begin catch
    print 'error'
end catch

第一次运行代码:

作业test2"成功启动.

Job 'test2' started successfully.

第二次运行代码(10 秒内):

Second run of the code (within 10 seconds):

消息 22022，级别 16，状态 1，第 0 行
SQLServerAgent 错误:运行作业 test2 的请求(来自用户 sa)被拒绝，因为该作业已根据用户 sa 的请求运行.

Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job test2 (from User sa) refused because the job is already running from a request by User sa.

为什么 TRY/CATCH 在这种情况下不起作用?

Why does TRY/CATCH not work in this scenario?

更新:我应该首先补充说，我正在使用具有链接服务器(sql server 2000)的 sql server 2005.我试图在 sql server 2005 服务器上编写一个 proc 来查看所有链接服务器上的作业.如果作业未运行，请运行它.最初，我使用 try - catch 并希望在运行已运行的作业但失败(此线程)时捕获任何错误.

UPDATE: I should added at first that I am working on a sql server 2005 which has linked servers (sql server 2000). I was trying to write a proc on the sql server 2005 server to see a job on all the linked servers. If the job is not running, run it. Initially, I used try - catch and hoped to catch any error when run the already running job but failed (this thread).

我最终使用了以下代码:(它不会编译，您需要替换一些变量，只是提供一个想法)

I finally used following code: (it won't compile, you need to substitute some variables, just gives an idea)

    CREATE TABLE [dbo].[#jobInfo](
        [job_id] [uniqueidentifier] NULL,
        [originating_server] [nvarchar](30) ,
        [name] [nvarchar](128) ,
        [enabled] [tinyint] NULL,
        [description] [nvarchar](512) ,
        [start_step_id] [int] NULL,
        [category] [nvarchar](128) ,
        [owner] [nvarchar](128) ,
        [notify_level_eventlog] [int] NULL,
        [notify_level_email] [int] NULL,
        [notify_level_netsend] [int] NULL,
        [notify_level_page] [int] NULL,
        [notify_email_operator] [nvarchar](128) ,
        [notify_netsend_operator] [nvarchar](128) ,
        [notify_page_operator] [nvarchar](128) ,
        [delete_level] [int] NULL,
        [date_created] [datetime] NULL,
        [date_modified] [datetime] NULL,
        [version_number] [int] NULL,
        [last_run_date] [int] NOT NULL,
        [last_run_time] [int] NOT NULL,
        [last_run_outcome] [int] NOT NULL,
        [next_run_date] [int] NOT NULL,
        [next_run_time] [int] NOT NULL,
        [next_run_schedule_id] [int] NOT NULL,
        [current_execution_status] [int] NOT NULL,
        [current_execution_step] [nvarchar](128) ,
        [current_retry_attempt] [int] NOT NULL,
        [has_step] [int] NULL,
        [has_schedule] [int] NULL,
        [has_target] [int] NULL,
        [type] [int] NOT NULL
    )


    SET @sql = 
    'INSERT INTO #jobInfo
    SELECT * FROM OPENQUERY( [' + @srvName + '],''set fmtonly off exec msdb.dbo.sp_help_job'')'

    EXEC(@sql)

    IF EXISTS (select * from #jobInfo WHERE [name] = @jobName AND current_execution_status IN (4,5)) -- 4: idle, 5: suspended 
    BEGIN
        SET @sql = 'EXEC [' + @srvName + '].msdb.dbo.sp_start_job @job_name = ''' + @jobName + ''''
        --print @sql    
        EXEC (@sql) 
        INSERT INTO #result (srvName ,status ) VALUES (@srvName, 'Job started.')
    END ELSE BEGIN
        INSERT INTO #result (srvName ,status ) VALUES (@srvName, 'Job is running already. No action taken.')
    END

推荐答案

并非所有错误都可以被 TRY/CATCH 捕获.在这种情况下，sp_start_job 实际上调用了外部过程，这些超出了 SQL Server 错误处理的范围.或者至少这是他们坚持的故事:

Not all errors can be caught by TRY/CATCH. In this case, sp_start_job actually calls external procedures, and these are outside the bounds of SQL Server's error handling. Or at least that's the story that they're sticking to:

http://connect.microsoft.com/SQLServer/feedback/details/362112/sp-start-job-error-handling

另请注意，这仍然是 SQL Server 2012 SP1 CU3 中的问题.如果您希望修复此错误，请投票和评论.

Also note that this is still a problem in SQL Server 2012 SP1 CU3. Please vote and comment if you want this bug fixed.

一种乏味但可行的解决方法，它需要一定的权限，在这种情况下假设作业所有者是 sa:

A tedious but viable workaround, which requires certain permissions and in this case assumes the job owner is sa:

DECLARE @x TABLE
(
  a VARBINARY(32),b INT,c INT,d INT,e INT,f INT,g INT,h INT,i NVARCHAR(64),
  Running BIT, -- the only important column
  k INT,l INT,m INT
);

DECLARE @job_id UNIQUEIDENTIFIER;

SELECT @job_id = job_id FROM msdb.dbo.sysjobs WHERE name = N'test2';

INSERT @x EXEC master.dbo.xp_sqlagent_enum_jobs 1, N'sa', @job_id;

IF EXISTS (SELECT 1 FROM @x WHERE Running = 0)
BEGIN
     EXEC msdb.dbo.sp_start_job @job_name = N'test2';
END
ELSE
BEGIN
     PRINT 'error';
END

更好的可能是:

DECLARE @job_id UNIQUEIDENTIFIER, @d DATETIME;

SELECT @job_id = job_id FROM msdb.dbo.sysjobs WHERE name = N'test2';

SELECT @d = stop_execution_date 
  FROM msdb.dbo.sysjobactivity WHERE job_id = @job_id;

IF @d IS NOT NULL
BEGIN
     EXEC msdb.dbo.sp_start_job @job_name = N'test2';
END
ELSE
BEGIN
     PRINT 'error';
END

在任何一种情况下，作业仍有可能在检查其状态和调用启动它之间开始，因此这并不能完全消除 sp_start_job 中的错误，但它使它们发生的可能性要小得多.

In either case, it is still possible that the job has started between the check for its status and the call to start it, so this doesn't eliminate errors from sp_start_job altogether, but it makes them far less likely to occur.

这篇关于TRY/CATCH 对 SQL Server 代理错误不起作用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！