Unable to ROLLBACK the transaction in Sql Server 2008R2?

Question

My Test table has two columns:

id         int not null  
somecolumn varchar(10))

Now see my query:

USE TRY
BEGIN TRANSACTION T1
INSERT INTO Test VALUES(7,'hi');
GO
INSERT INTO Test VALUES(8,'hi','ABC');
GO
PRINT @@ERROR
if @@ERROR>0
    ROLLBACK TRANSACTION T1
ELSE
    COMMIT TRANSACTION T1

I know that my second query is wrong so I want transaction to rollback but it inserts the first query then shows this message:

(1 row(s) affected)  
Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.
213

you can't use a GO keyword inbetween a BEGIN and END. [http://stackoverflow.com/questions/1180279/when-do-i-need-to-use-begin-end-blocks-and-the-go-keyword-in-sql-server][1] [1]: http://stackoverflow.com/questions/1180279/when-do-i-need-to-use-begin-end-blocks-and-the-go-keyword-in-sql-server — , Jul 03 '12 at 22:40

score 0 · Answer 1 · answered Jul 16 '12 at 07:07

As already stated by Benedikt, we cannot use GO in between BEGIN and END. If you remove GO from the above transaction the statements will fail even to compile because of wrong insert statement written. TRY..CATCH won't work either (if you try to catch the error and rollback transaction during execution phase).

Unable to ROLLBACK the transaction in Sql Server 2008R2?

1 Answers1