Transaction Error Handling - 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 2005 Forums

Transact-SQL (2005)

Transaction Error Handling

Author

Topic

SQL_Deepak
Starting Member

9 Posts

Posted - 2010-03-24 : 16:40:59

Hi

I have my stored procedure written in following format:

***************************************
Create Stored procedure(param1..param n)
as
begin try
begin transaction
Statement 1
Statement 2
.
.
.
Statement n
Commit Transaction
end try

begin catch
IF( @@TRANCOUNT > 0)
ROLLBACK TRANSACTION
end catch
Return
*******************************************

In one of the statements i have included an invalid table name.

when i run this stored procedure in a query window it gives me following error:
***************************************************************
Msg 208, Level 16, State 1, Procedure PAY_AGENT_COMMISION, Line 275
Invalid object name 'TransactionLog22'.
Msg 266, Level 16, State 2, Procedure PAY_AGENT_COMMISION, Line 275
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
***************************************************************

Also note that when I close the query window i get the following prompt:
"There are uncommitted transactions. Do you wish to commit these transactions before closing the window?"

Can anyone please help me what could i be doing wrong here. Note that just above the error statement I am printing the @@trancount and its giving 1.

Please help.

Thanx..
Deepakl

Deepak

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-03-24 : 17:37:34

You have started a transaction, then experienced an error that threw an exception before the transaction could commit or rollback. So... you still have a transaction that is uncommitted. Are you intentionally using the invalid table name to induce the error?

The TRY-CATCH block does NOT handle errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution. (per BOL)

=======================================
There are no passengers on spaceship earth. We are all crew. -Marshall McLuhan, educator and philosopher (1911-1980)

SQL_Deepak
Starting Member

9 Posts

Posted - 2010-03-24 : 17:51:08

Thanx Bustaz..

This means that my code structure is correct. If there are any other type of exception then it will work fine. I will try to generate some errors in some other way.

Will see if it works.

Thank You

Deepak

SQL_Deepak
Starting Member

9 Posts

Posted - 2010-03-24 : 18:00:36

wow this is awesome.

In the SP I tried to insert a string in column with data type int and it worked as expected.

Thanx for help..

cheers

Deepak

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources