Hi Everyone!

I need your help and brains.

I have this stored procedure which run every morning for data extraction to the other SQL Server (version 2005). This stored procedure is set in the Job.

The Stored Procedure contains 4 stored procedure with different processess in extracting data. Most queries inside uses Table variables, temporary tables and table functions.

All the queries is running perfectly and no errors so far.

This is the sample SP:

ALTER PROCEDURE spTEST
as
Begin

Declare @date datetime

set @date = getdate()

exec sp1 @date
exec sp2 @date
exec sp3 @date
exec sp4 @date

END

Now, the problem is, there are times that the Stored procedures inside it stop executing because the volume of data to retrieve is large or the Main database where the SP pulls the data is busy because there are other transactions going on although our System Administrator tells me that during that time of data extraction, there are no traffic or other transactions running.

With this problem, not entire Stored procedures are executed leaving the Tables that supposed to have a data is empty and some has data that is successfully executed during one of the SP.

So when this happen i have to rerun manually the entire SP and i HAVE TO delete data in each tables the SP will store its data before i run the SP again.
Its very tiring and time consuming. Im thinking that whenever an error occurs during the SP execution and exited out because of an error or timeout, I want to rollback all changes happened in the SP.

This is where the Commit and Rollback transaction comes in but i don't know how to use it and where should i put it.

Given in the SP code i provided above, where should i put it? can you guys revised the code so i can study it and use it.. and lastly, do you think its also best that i have to included each SP's inside the main SP the commit and rollback transaction? and how?

please help me guys and provide me some samples. I know your all GURU in this..

I appreciate your help.