The Transaction never ENDS (Horror)

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

Transact-SQL (2000)

The Transaction never ENDS (Horror)

Author

Topic

lock
Starting Member

1 Post

Posted - 2003-07-30 : 11:19:16

Hello,

I have a stored proc at SQL SERVER 7.0



BEGIN TRANSACTION

DECLARE @unique_id int
DECLARE @error int

EXEC @unique_id =spNewGuideID

INSERT INTO tblGuide(g_id,city_id,state_id,[name],lastName,sex,education,
registeringStyle_id,registeringDate,isActive)
VALUES (@unique_id,@city_id,@state_id,@name,@lastName,@sex,@education,
@registeringStyle_id,@registeringDate,@isActive)

SET @error = @@ERROR

IF @error!=0 
BEGIN
	ROLLBACK TRANSACTION	
	PRINT '1'
END
ELSE
BEGIN
	COMMIT TRANSACTION
	PRINT '0'
END

and spNewUniqueID


CREATE PROCEDURE spGetNewUniqueID 
AS

UPDATE tblAutoIncrement
SET lastValue=lastValue+1 WHERE tableName = 'tblGuide'

DECLARE @RC int
DECLARE @unique_id int

SET @unique_id = (SELECT lastValue FROM tblAutoIncrement 
WHERE tableName ='tblGuide')

SET @RC = (SELECT COUNT(*) FROM tblGuide WHERE g_id =@unique_id)

IF @RC != 0
	RAISERROR('FATAL CONCURRENCY ERROR',16,1)
ELSE
	RETURN @unique_id

It looks like somewhat stupid but must work fine?

Anyway when i run first stored proc I get an error message :

"Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing....."

Why? Please help me.

Thanks.

SELECT * FROM life
WHERE human=me

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-30 : 11:40:40

Check out

SELECT @@TRANCOUNT

You've left one out there uncommitted

Just do ROLLBACK TRAN

Brett

8-)

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-07-30 : 13:01:49

Firstly, you seem to be replicating the functionality of an IDENTITY column in the second stored proc. Couldn't you make tblGuide.g_id an IDENTITY column? SQL Server will take care of generating the incrementing values for you, and you will be spared all this trouble.

Coming to your problem, nesting stored procedures is slightly tricky when it comes to transaction handling. You are not handling any errors occurring from calling spGetNewUniqueID, you have the error-handler only after the INSERT statement. The procedure exits when the error is thrown by spGetNewUniqueID, and the calling procedure never get a chance to rollback the open transaction. Hence the error message. Look at the documentation in Books online for SET XACT_ABORT ON | OFF.

Also, take a look at this thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=27001

Owais

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources