CODEWITHSUNDEEP
sql-server-2005transactions
ZZZ
ZZZ

Reputation: 3764

A simple transaction doesn't work on SQL Server 2005

I am doing this in SQL Server 2005. I have a table, there is only one column of type int, o you cannot insert char in it.

DECLARE @intErrorCode INT

BEGIN TRAN
Update TestTable set A='a' where A=3 --UPDATE TO CHAR, FAIL

SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM

COMMIT TRAN

PROBLEM:
IF (@intErrorCode <> 0) BEGIN
PRINT 'Unexpected error occurred!'
ROLLBACK TRAN
END

I am expecting to see 'Unexpected error occurred!' as result. But what I actually see is: Conversion failed when converting the varchar value 'a' to data type int.

It seems that my SQL Server doesn't take my code as a transaction at all... It hit the fail line and quit running right after that.

What am I doing wrong here?

Upvotes: 0

Views: 166

Answers (1)

HLGEM
HLGEM

Reputation: 96640

First use Try catch blocks instead.

However, no sql error trapping will work for all types of errors.

Read about error handling using the try catch block in Books online and you will see a discussion of what types of errors are not trapped.

Also it is better to return the actual error than a general message usually. It is much easier to track down teh error issue when you know the real error.

Upvotes: 2

Related Questions