Force SQL Agent to fail

Transact-SQL
1

Hi All,

I have a question, I have a sql agent that when step 1 fails it shows Cancelled in the last run outcome. I did some research and found that by adding a RAISERROR() would set the step to fail so I went and made the changes but even though the job fails it reports Success and still displays Cancelled in the last run outcome. below is my script.

declare @result as integer


exec @result= sp_stop_job @job_name = 'Returns Processing' 

if (select count(*) FROM (SELECT tempdb.dbo.DEX_LOCK.table_path_name
FROM         DYNAMICS.dbo.ACTIVITY INNER JOIN
                      tempdb.dbo.DEX_LOCK ON DYNAMICS.dbo.ACTIVITY.SQLSESID = tempdb.dbo.DEX_LOCK.session_id
WHERE     (tempdb.dbo.DEX_LOCK.table_path_name LIKE '%SVC%')) AS subquqery) <> 0
begin
	if @result = 1
	begin
		RAISERROR('Return Processing Sql Agent has fail on step 1',16, 1)
	end
end

	-- Check if we are posting receipts

if (select count(*) FROM (SELECT RETDOCID FROM [CGI].[dbo].[SVC05015]) AS subquqery) <> 0
begin
	if @result = 1
	begin
		RAISERROR('Return Processing Sql Agent has fail on step 1',16,1 )
	end
end

Thanks in advance,

Eleazar R

2

Longshot:

Are there other steps in the job? and the ACTION on the (error) step set to "Continue with next step"?

3

Thank you for your response Kristen. To answer your question yes they are other steps in this job and they are all set to On success action Go to the next step and On failure Action quit the job reporting failure. But the main issue is that even when it fails it reports success and stops the job.

Thank you,

Eleazar R

4

It looks like the first thing you do is stop the job - that is why you see the job as cancelled.

exec @result= sp_stop_job @job_name = 'Returns Processing'

1 Like
5

Thank you jeffw8713. I totally missed that, as you can tell I'm still a rookie.