SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Problem related to transaction


Problem related to transaction

Author
Message
Shadab Shah
Shadab Shah
SSC-Addicted
SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)

Group: General Forum Members
Points: 439 Visits: 798
Hi all,
My friend ask me the following transaction problem. Consider the following structure of tables as
Table1(int)
Table2(int)
Table3(int)

Now consider the following problem.



CREATE procedure [dbo].[insert_SP]
AS
DECLARE @errorcode int
BEGIN TRAN t1
INSERT INTO TaBLE1 VALUES(2)
INSERT INTO TaBLE2 VALUES('A')
SELECT @errorcode = @@ERROR
PRINT @errorcode
IF(@errorcode<>0)
BEGIN
GOTO PROBLEM
END
INSERT INTO TaBLE3 VALUES(3)
COMMIT TRAN t1
PROBLEM:
IF(@errorcode<>0)
BEGIN
PRINT 'Unexpected error occured'
ROLLBACK TRAN t1
END

GO






Since i am trying to insert Character in Integer it would give me the error hence i would expect that the Statement 'Unexpected error occured' should execute. But that is not the case over here. I notice that transactions are rollback , but it should show the error message as 'Unexpected error occured', which is not happening. I would like to know why this error is occuring.
John Mitchell-245523
John Mitchell-245523
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14097 Visits: 15950
Looks like the conversion error is aborting the batch before the error handling is reached. Have you tried TRY...CATCH instead?

John
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39247 Visits: 38529
FIrst, you really need to read the first article I reference below in my signature block regarding asking for help. You may have provided your code (and I am saying your code because I am tired of people saying "a friend" when they post a question), but you did not provide anything else for people to really use in helping you.

Second, the procedure aborts with this error so it never gets to the error checking:

Msg 245, Level 16, State 1, Procedure insert_SP, Line 7
Conversion failed when converting the varchar value 'A' to data type int.

You should consider using a TRY CATCH block as in the following:



create table dbo.table1(aCol int);

create table dbo.table2(aCol int);

create table dbo.table3(aCol int);

go
CREATE procedure [dbo].[insert_SP]
AS
BEGIN
DECLARE @errorcode int
BEGIN TRAN t1
begin try
INSERT INTO dbo.table1 VALUES(2)
INSERT INTO dbo.table2 VALUES('A')
INSERT INTO dbo.table3 VALUES(3)
COMMIT TRAN t1
end try
begin catch
PRINT 'Unexpected error occured'
ROLLBACK TRAN t1
end catch
END

GO

exec dbo.insert_SP;
go

drop procedure dbo.insert_SP;
go

drop table dbo.table1;
drop table dbo.table2;
drop table dbo.table3;
go




Please notice how I setup the create statements for the tables and at the end dropped them. This helps those of us help you to keep our sandbox databases clean.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
sqlnaive
sqlnaive
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4277 Visits: 2774
Perfectly described Lynn.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search