Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problem related to transaction Expand / Collapse
Author
Message
Posted Thursday, December 27, 2012 6:26 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 12:29 AM
Points: 220, Visits: 537
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.
Post #1400595
Posted Thursday, December 27, 2012 6:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:58 AM
Points: 5,077, Visits: 8,919
Looks like the conversion error is aborting the batch before the error handling is reached. Have you tried TRY...CATCH instead?

John
Post #1400603
Posted Thursday, December 27, 2012 7:09 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:44 PM
Points: 22,525, Visits: 30,290
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.



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)
Post #1400609
Posted Friday, December 28, 2012 3:11 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 6:04 AM
Points: 3,517, Visits: 2,606
Perfectly described Lynn.
Post #1400848
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse