Using Commit and Rollback in SQL Server 2005

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.

Can I suggest an alternative for you - have you looked at implementing SSIS in order to transfer your data across at all?

Usually you would implement transactions within the individual stored procedures. I've no idea whether the inserts performed within a nested stored procedure would rollback or not here. You can try this though - it'd be interesting to know whether this is possible - if you wouldn't mind posting the answer when you've tested it please :)

Please note this might need a little tweaking as it's from memory (EDIT: 1st edit made following the comments in subsequent posts below) and I've not got SQL Server here. This code assumes your nested stored procedures return a value which you can query for a success indication.

Code:

---

DECLARE @nestedSProcRetVal as int
SET @nestedSProcRetVal = 0

BEGIN TRANSACTION monitorNestedSProcs
EXECUTE @nestedSProcRetVal = sp1 @date

IF (@nestedSProcRetVal <> 0)
  BEGIN
    ROLLBACK TRANSACTION monitorNestedSProcs
  END
ELSE
  BEGIN
    EXECUTE @nestedSProcRetVal = sp2 @date

  IF (@nestedSProcRetVal <> 0)
    BEGIN
      ROLLBACK TRANSACTION monitorNestedSProcs
    END
  ELSE
    BEGIN
      EXECUTE @nestedSProcRetVal = sp3 @date

    IF (@nestedSProcRetVal <> 0)
      BEGIN
        ROLLBACK TRANSACTION monitorNestedSProcs
      END
    ELSE
      BEGIN
        EXECUTE @nestedSProcRetVal = sp4 @date

      IF (@nestedSProcRetVal <> 0)
        BEGIN
          ROLLBACK TRANSACTION monitorNestedSProcs
        END
      ELSE
        BEGIN
          COMMIT TRANSACTION monitorNestedSProcs
        END

---

Quote:

---

I've no idea whether the inserts performed within a nested stored procedure would rollback or not here.

---

They do. Not only that but you could even roll back the transaction from within the individual nested sprocs even though it was begun in the calling sproc or you could roll a transaction that was begun in a nested sproc back in the calling sproc. Basically, a commit or rollback will take the last Begin Transaction as it's start point regardless of where it actually occurred. It's confusing to maintain if you start doing that so I'd recommend keeping them at the same level as Alex has suggested.

As an extra pointer, if you want to return values in the way Alex has suggested you will actually want to implement UDF's (User Defined Functions) rather than Sprocs.

Nice one - thanks clarifying that. Useful knowledge - thanks for sharing it! :thumb: :)

Thanks Guys!

Im looking into it now.

Question, so basically, whenever an error occur during one of these stored proc, all the changes made starting from the point of execution in the first stored proc to the last will rollback?

am i right?

Hey Guys,

what seems to be the problem, this is my code.

DECLARE @Date datetime
Set @Date = getdate()
DECLARE @nestedSProcRetVal int
Set @nestedSProcRetVal = 0

@nestedSProcRetVal= exec sp1 @Date

The error returns this :

Msg 102, Level 15, State 1, Procedure spDataTransferJob, Line 14
Incorrect syntax near '@nestedSProcRetVal'.

any idea?

Well I did put in a disclaimer that the above was from memory :p There's a sample at post 5 here http://www.vbforums.com/showthread.p...ored+procedure, and I should have told you this syntax - my appologies!

Code:

---

EXEC @nestedSProcRetVal= sp1 @Date

---

Quote:

---

Originally Posted by tovbornotovb

Question, so basically, whenever an error occur during one of these stored proc, all the changes made starting from the point of execution in the first stored proc to the last will rollback?

---

Since the Begin transaction call, yes - any inserts, updates or deletions to that specific transaction being rolled back will be removed from the log/*.ldf database and never committed/submitted/occur on the full (*.mdf) database.