SQL Server Performance Forum – Threads Archive

Rollback transaction after SQL Jobs executed T-SQL

Hello everyone, I am facing issue related to Job execution (T-SQL).I have one SP for creating SQL jobs and exec it. now my issue is that i want to rollback the transaction in case error occur while executing T-SQL inside the job.My SP code as below CREATE proc AddTestJob(@callback int output) as
Declare @jbname varchar(20),
@cmd varchar(20),
@cmdsuccess int set @jbName = ‘Runlast’
BEGIN EXEC msdb.dbo.sp_add_job @job_name = @jbName, @enabled = 1, @delete_level = 1 EXEC msdb.dbo.sp_add_jobserver @[email protected], @server_name='(local)’ SET @cmd = ‘EXEC testinginsert ‘ EXEC @callback=msdb.dbo.sp_add_jobstep @job_name = @jbName, @step_name = ‘Run First’, @command =
@cmd,@database_name = ‘Pubs’ /*************Here i want to raiserror when Sp: EXEC TestingInsert does not exist**************/
EXEC msdb.dbo.sp_start_job @job_name = @jbName END
I am able to get the return code of msdb.dbo.sp_start_job & msdb.dbo.sp_add_jobstep but they are related to the execution of start & steps but not related to execution of @cmd command. My requirement is that in msdb.dbo.sp_add_jobstep if @cmd fails then it should rollback all the transaction from where i have called AddtestJob sp. Note: I am calling above sp in other SP which contain transaction handling and my last execution is above sp if this goes fine i am committing data else i have to rollback.

---

While executing
EXEC msdb.dbo.sp_add_job @job_name = @jbName, @enabled = 1, @delete_level = 1 check for job id which is returned as output.
it Is the job identification number assigned to the job if created successfully.
So check this and if null then raise error.

---

Thnaks for you answer ranjit. I have tried your solution but found not working. Actually my Jobs is getting created successfully and my steps is also executing successfully but my issue here that i am not able track what happened to T-SQL i have passed in sp_add_jobstep whether it got executed succefully or there is any error. Thanks and Regards Ravi K

---

Check for the return code declare @retcode int
exec @retcode=sp_add_jobstep if @retcode<>0
raiseerror
return code 0 success and 1 failure

---

]]>