Strange Transaction Issues - 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)

Strange Transaction Issues

Author

Topic

Pace
Constraint Violating Yak Guru

264 Posts

Posted - 2007-08-14 : 04:21:31

Please can you tell me why the following fails and the subsequent works?

Fails;

 
BEGIN TRANSACTION 
UPDATE
	Product
SET
	ModelNo = [Product Code]
IF (@@ERROR <> 0) 
	BEGIN 
		COMMIT TRANSACTION 
			PRINT 'Successfully updated'
	END
ELSE
	BEGIN
		ROLLBACK TRANSACTION 
			PRINT 'Error'
	END
GO

Works;


BEGIN TRAN 
	UPDATE 
		Product 
	SET
		ModelNo=[Product Code]
COMMIT TRAN

"Impossible is Nothing"

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2007-08-14 : 04:28:19

Why are you committing on an error and printing successfull?

You do know that @@ERROR <> 0 means Error and @@ERROR = 0 means successfull - don't you?
Duane.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2007-08-14 : 04:50:19

Ya ditch is right.

When @@ERROR <> 0 ' it means that an error occured.
If no error occured then you are rollbacking the transaction.

In your case the value of @@Error may be equal to 0. Since you are rollbacking the transaction, you are not seeing the correct result in the table.

Pace
Constraint Violating Yak Guru

264 Posts

Posted - 2007-08-14 : 04:57:11

quote:
Originally posted by ditch

Why are you committing on an error and printing successfull?

You do know that @@ERROR <> 0 means Error and @@ERROR = 0 means successfull - don't you?
Duane.

Thanks, you cleared this up for me

"Impossible is Nothing"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources