April 16, 2009 at 4:58 am
hi,
I have a probelem in SQL server 2005.
I have made a Sql server Agent JOB, but when it fails it desnot rollback the changes made in the database.
For Example: It has 10 steps but if it fails on 5th step then database remains in that state only.
Is it possible if it fail on step 5 it rollback the changes made by it in the database.i.e., Can it bring the database in state before the start of step 1.
April 16, 2009 at 3:51 pm
It would be gud if you include the steps which are inter-dependent in one job step.
MJ
April 16, 2009 at 4:15 pm
No - each job step is it's own transaction. I don't think you can use transactions because each step could also be using a new connection.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
April 16, 2009 at 4:17 pm
Hi,
It seems that you have properly creating a job for Agent...
You need to follow the step again then might it works.
Execute sp_add_job to create a job.
Execute sp_add_jobstep to create one or more job steps.
Execute sp_add_schedule to create a schedule.
Execute sp_attach_schedule to attach a schedule to the job.
Execute sp_add_jobserver to set the server for the job.
need to go for other link aslo........
http://msdn.microsoft.com/en-us/library/ms187961.aspx
http://msdn.microsoft.com/en-us/library/ms182079.aspx
http://msdn.microsoft.com/en-us/library/ms187358.aspx
http://msdn.microsoft.com/en-us/library/ms186766.aspx
http://msdn.microsoft.com/en-us/library/ms178625.aspx
🙂
April 16, 2009 at 9:53 pm
The job is made & it runs successfully now, each step of it has some T-Sql queries which run.
But if it fails then the database remains in that state only, while I want if it fails it should rollback all changes made in the database.
Can this be done by invoking the job in a transaction which is in a step of another job. :ermm:
June 11, 2014 at 12:41 pm
I would say that a SQL Server job does not roll back the preceding steps when you encounter a step that fails. You can add all your SP calls to tasks in a SSIS package. At the package and the tasks set the TransactionOption property to "supported." Depending on your connection(s) it may be a DTC trasnsaction (across servers). I have found these to be troublesome in SSIS 2005,just FYI. So the connections to the same server in your packege are a safe bet.
----------------------------------------------------
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply