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,