SQL Agent Job succeeds even though step should fail
-
Would it work to have your job step wrap the execution of the remote SP in some local code; the wrapper would check for a return code indicating successful execution of the SP? No return code, RAISERROR.
Just thinking out loud, haven't tested anything here....
Rich
-
So far as I know, it's not possible to pass an error outside of a jobstep to cause the job itself to fail. The error is only relevant inside the session of the current step.
If anyone has tested code they can present, I'd certainly take a look at it. Right now, though, I have given up on this particular issue until it rears its head again. I'm overloaded with projects at the moment and don't have time to test suggestions.
When I do get freed up, I will definitely take another look at this thread.
-
Brandie, here's a link I found that suggests it is possible to fail a job step with RAISERROR:
http://www.bidn.com/blogs/Daniel/ssas/2313/force-a-sql-agent-job-step-to-fail
I just tested this by creating a dummy Agent job with 2 steps. The first step contained the code from that URL, with
IF 1=1to trigger the RAISERROR.Indeed, the job failed and the error was raised, as shown in the job history.
HTH,
Rich
P.S. For this to work, you have to set the job step's Action on Failure to "Quit the Job Reporting Failure"
-
rmechaber (7/10/2012)
Brandie, here's a link I found that suggests it is possible to fail a job step with RAISERROR:http://www.bidn.com/blogs/Daniel/ssas/2313/force-a-sql-agent-job-step-to-fail
I just tested this by creating a dummy Agent job with 2 steps. The first step contained the code from that URL, with
IF 1=1to trigger the RAISERROR.Indeed, the job failed and the error was raised, as shown in the job history.
How does one work that with a timeout?
-
I am having the same behaviour with SQL Server 2012 RTM.
I came up with the following workaround for my environment. This code should go into a job step immediately after the step with the remote server statement and will generate a fail if the previous step suffered a remote query timeout.
DECLARE @JobID UNIQUEIDENTIFIER,@Message VARCHAR(MAX);
SELECT @JobID = $(ESCAPE_NONE(JOBID));
WITH CTE AS (
SELECTja.message,
ROW_NUMBER() OVER (PARTITION BY j.name ORDER BY ja.run_date DESC, ja.run_time DESC) AS RowNo
FROMmsdb..sysjobs j
JOINmsdb..sysjobhistory ja
ON j.job_id = ja.job_id
WHERE j.job_id = @JobID)
SELECT @message = message
FROM CTE
WHERERowNo = 1;
IF @message LIKE '%Query timeout expired%'
BEGIN
RAISERROR('Timeout', 16, -1);
END;
-
Thanks, Sean. I'll take a look at that code.
-
Hi,
I have come across same problem last week and found a solution, Dont know this helps or not, because my Proc in the job step, Times out at a step where it has to call a remote proc.
Declare @sql nVarchar,@Remote_ReturnValue int,@ReturnValue int
SET @sql= N'EXEC @Remote_ReturnValue= [' + @LinkedServer + '].[' + @LinkedDatabase + '].[dbo].' + @Table
EXEC sp_executesql
@Statement= @sql
,@params=N'@Remote_ReturnValue INT OUT'
,@Remote_ReturnValue=@ReturnValue OUT
IF (@ReturnValue <> 0 OR @ReturnValue IS NULL)
BEGIN
RAISERROR(' The linked server timed out!!!!!',16,1)
END
Hope this helps
This has been tested and going to be released into production
Viewing 7 posts - 31 through 36 (of 36 total)
You must be logged in to reply to this topic. Login to reply