TRY/CATCH does not work on SQL Server Agent error?

Question

I use sp_start_job to start a job.

The job (test2) has only one step:

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

The TRY/CATCH code:

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

First run of the code:

Job 'test2' started successfully.

Second run of the code (within 10 seconds):

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.

Why does TRY/CATCH not work in this scenario?

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

Classify

Answer 1

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

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

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

Even better might be:

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

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.

Sniperscope

If without INSERT @x, then it works. But when with INSERT @x, I got – Classify

If you get an error inserting into @x, you may be running a different version than I am. Take a look at the column output for the XP; you may have to drop a column or two or fix a data type (I have no idea what error you're getting). I do not have a handy inventory of how the interface to this XP may have changed from version to version. In any case, the second code sample is, like I said, probably better anyway. – Sniperscope

I mean I run these on a linked server (SQL Server 2000) from a local server (2005), so the statement actually is [linkedServer].master.dbo.xp_sqlagent_enum_jobs, and I got following error OLE DB provider "SQLNCLI" for linked server "hp-dublin" returned message "The partner transaction manager has disabled its support for remote/network transactions.". Msg 7391, Level 16, State 2, Line 12 The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "hp-dublin" was unable to begin a distributed transaction. – Classify

If without INSERT @x, it runs ok – Classify

Yeah if you are on a linked server, and executing an XP remotely returns an error, then either (a) encapsulate that code in a stored procedure on the remote server, or (b) once again use the better method I suggested. Do I need to remove the first code sample from my answer so you stop trying it? I thought it might be useful for other readers. Also if you are trying to do this across linked servers, you might want to include that information in the question. – Sniperscope

There is no sysjobactivity on sql server 2000, so the better method won't work. And I can't modify anything on remote servers (linked servers), which is the problem. – Classify

And more new information - SQL Server 2000? shrug You may have to handle this elsewhere. You're changing all of the requirements and every variable you add makes it harder to solve your problem. In the future please state all of these additional requirements up front. – Sniperscope

Sorry, I should added this at the beginning. But good news I managed to work it out. See update. – Classify

Answer 2

You can use alerts to execute SQL Agent jobs. And you can use alerts' options to configure required response. (this will help you to totally avoid error 22022 but you will have additional records in the errorlog)

Illuminator

What "Alerts" are you talking about? – Rumilly

You tried to execute job using sp_start_job. But it's possible to do this using alerts. You can create alert (sp_add_alert), specify your job as alert's "response" then activate that alert in any way (raiserror is good enough). And pay your attention to the "delay" option of alert. This way you can get "alternative" job activation. – Illuminator

With RAISERROR, you need WITH LOG for this to work, which requires sa-level permissions, not unlike the perms needed to run sp_start_job. In my testing, no delay is required since the Agent will just process the alerts using the job in series. Great trick! – Bazluke

create login test with password = '1', check_policy = off go execute as login = 'test' go raiserror( '1', 1, 1 ) with log go revert go create proc proc_delme as raiserror( '1', 1, 1 ) with log go create user test from login test with default_schema = dbo go grant execute on proc_delme to test go execute as login = 'test' go exec proc_delme go revert go drop user test go drop login test go – Illuminator

Sorry, you are right, but you can create procedure with that "raiserror" and that works. I tried to add sample code in the comment above. – Illuminator

Answer 3

To make an evergreen solution, we should also consider a job that has never run before. This my be useful in a scenario where the SQL server is rebuilt and the jobs recreated. To capture that scenario look at the last run requested date:

DECLARE @jobEnd DATETIME, @jobRun DATETIME
SELECT @jobEnd = sja.stop_execution_date , @jobRun = sja.run_requested_date
FROM msdb.dbo.sysjobactivity AS sja
INNER JOIN msdb.dbo.sysjobs AS sj
ON sja.job_id = sj.job_id
WHERE sj.name = 'PhoneListSyncMember'

IF (@jobEnd IS NOT NULL AND @jobRun IS NOT NULL)
  OR (@jobEnd IS NULL AND @jobRun IS NULL)  -- job is New and never run before
    EXEC @iRet =msdb.dbo.sp_start_job @job_name='PhoneListSyncMember'

Dad

Answer 4

Here is how you can capture Job trigger failures.

Declare @returnstatus int

Exec @returnstatus msdb.dbo.sp_start_job 'Jobname'

if(@returnstaus = 1) Print 'Success' else Print 'Failure'

Benediction