Transactions issue -

[sanjdhiman]

I have the following piece of code.

I am making it error by having an unknown table BondquoteArchive to see if the transaction gets rolled back, however it does not. Can anyone see why????

Thanks

DECLARE @ErrorCounter INT
SELECT @ErrorCounter = 0
BEGIN TRAN
INSERT INTO ClientBondQuoteArchive
SELECT * FROM ClientBondQuote
TRUNCATE TABLE ClientBondQuote

SELECT @ErrorCounter + @@ERROR

INSERT INTO BondQuoteArchive
SELECT * FROM BondQuote

SELECT @ErrorCounter = @ErrorCounter + @@ERROR

DELETE FROM BondQuote

SELECT @ErrorCounter = @ErrorCounter + @@ERROR

INSERT INTO BondQuoteOriginalArchive
SELECT * FROM BondQuoteOriginal

SELECT @ErrorCounter = @ErrorCounter + @@ERROR

TRUNCATE TABLE BondQuoteOriginal

SELECT @ErrorCounter = @ErrorCounter + @@ERROR

SELECT @ErrorCounter

IF @@TRANCOUNT > 0 AND @ErrorCounter = 0
BEGIN
PRINT 'Committing Tran'
COMMIT TRAN
END
ELSE
BEGIN
PRINT 'Rolling Back'
ROLLBACK TRAN
END

 

[bborissov]

Change this:
SELECT @ErrorCounter + @@ERROR
to
SELECT @ErrorCounter = @ErrorCounter + @@ERROR

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[sanjdhiman]

I made the change and it is still leaving an open transaction

SELECT @@TRANCOUNT returns 1.

So it has not rolled back

the error msg which is what I expect it to return is

SELECT @ErrorCounter = @ErrorCounter + @@ERROR

Server: Msg 208, Level 16, State 1, Line 10
Invalid object name 'BondQuoteArchive'.

So question is why is it not rolling back???

thanks

 

[bborissov]

Where you tryed this? IN QA or you create trigger first and then do the required action that fires that trigger? If you do this in QA it stops after first error occured and does not continue execution.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[sanjdhiman]

its in QA.

Yep it does that

I would have thought it would have rolled itself back.

Its actually part of a stored procedure.

 

[bborissov]

create a SP with just that code and run it to see what happens.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[sanjdhiman]

get the same error

Server: Msg 208, Level 16, State 1, Line 10
Invalid object name 'BondQuoteArchive'.

 

[bborissov]

Check SET XACT_ABORT { ON | OFF } in BOL.
Severity of this error is big and that is why SP stops. If you want just to test you SP use RAISERROR('some message',15,1) to simulate error. If you use SQL Sever 2005 you could close all commands in TRY ... CATCH block (Unfortunately i can't test this right now).


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.