Transaction Rollback - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Transaction Rollback

Author

Topic

skillile
Posting Yak Master

208 Posts

Posted - 2002-05-03 : 16:21:03

I have a simple trans that processes a select then insert. At each step I write an error return. I want the transaction to fail on error
but I want to write the error to a table on the error rollback. Can this be done. Thanks here is a sample.

BEGIN TRANSACTION extract

--**************************************************************************
--check to make sure serverid was passed step 1
--**************************************************************************
if @serverid < 1
begin
set @err = 1
set @msg='Server ID < 1 /' + convert(varchar(20),getdate(),109) + ' / Serverid Passed= ' + cast(@serverid as char(5)) + ' /failed at step 1'
goto error
end

--**************************************************************************
--set vars step 2
--**************************************************************************
set @err=0
set @servername=(select servername from tblservers where serverid= @serverid)
set @catalogname=(select catalogname from tblservers where serverid= @serverid)

if @@error <> 0 OR @err <> 0
begin
set @err = 2
set @msg='Variables Incorrect /' + convert(varchar(20),getdate(),109) + ' / Serverid Passed= ' + cast(@serverid as char(5)) + ' /failed at step 2'
goto ERROR
end

--**************************************************************************
--delete/pull order info step 3
--**************************************************************************

--set SQLstring

set @sqlString = 'select '''+
@catalogname + ''', ' +
convert(varchar(5),@serverid) +
', Fld.iFolder, blah blah
'

--First delete all orders from table that match @serverid step 3
--**************************************************************************

delete
from tblorders
where catalogid=@serverid

if @@error <> 0 OR @err <> 0
begin
set @err = 3
set @msg='Order Delete ***FAILED***- ' + @servername + @catalogname + ' /' + convert(varchar(20),getdate(),109) + ' / Serverid Passed= ' + cast(@serverid as char(5)) + ' /failed at step 3'
goto ERROR
end

--Now insert data into table step 4
--**************************************************************************

--insert data into table

insert into tblorders (
catalogname,
catalogid,
execute (@sqlString)

if @@error <> 0 OR @err <> 0
begin
set @err = 4
set @msg='Order INSERT ***FAILED***- ' + @servername + @catalogname + ' /' + convert(varchar(20),getdate(),109) + ' / Serverid Passed= ' + cast(@serverid as char(5)) + ' /failed at step 4'
goto ERROR
end

set @msg='DATA Extraction SUCCESS- ' + convert(varchar(20), getdate(),109)

insert into tblserverjournal (serverid, journalnote, journaltimestamp, journalcreatedby)
values (@serverid, @msg, getdate(), @@servername)

COMMIT TRANSACTION

--*****************************************************
--error return and write to journal
--*****************************************************
ERROR:

BEGIN TRANSACTION insertjournal
execute ps_insert_journal @serverid, @msg, @@servername
COMMIT TRANSACTION insertjournal

ROLLBACK TRANSACTION extract
return @err

slow down to move faster...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-03 : 16:57:36

Here's an article that describes a way to do this:

http://www.sqlteam.com/item.asp?ItemID=2290

Daniel also has some code in the Script Library that enhances this:

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=14623

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources