CODEWITHSUNDEEP
sql-servert-sqltransactions
Peter
Peter

Reputation: 25

Why does SQL Server transaction continue when error has occured?

I assumed an error will abort the transaction, but it does not. Why is table t2 created in code below?

IF OBJECT_ID('dbo.t1', 'U') IS NOT NULL 
    DROP TABLE dbo.t1

IF OBJECT_ID('dbo.t2', 'U') IS NOT NULL 
    DROP TABLE dbo.t2
GO

SELECT 
    OBJECT_ID(N't1', N'U') AS t1_Exists, 
    OBJECT_ID(N't2', N'U') AS t2_Exists
GO

BEGIN TRANSACTION 

CREATE TABLE t1 (id INT)
GO
CREATE TABLE t1 (id INT)--Causes an error, since t1 already exists
GO
CREATE TABLE t2 (id INT)
GO

COMMIT TRANSACTION 
GO

SELECT 
    OBJECT_ID(N't1', N'U') AS t1_Exists, 
    OBJECT_ID(N't2', N'U') AS t2_Exists

After running this, the table t2 exists. Here is the output:

t1_Exists   t2_Exists
----------- -----------
NULL        NULL

Msg 2714, Level 16, State 6, Line 1
There is already an object named 't1' in the database.

Msg 3902, Level 16, State 1, Line 2
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

t1_Exists   t2_Exists
----------- -----------
NULL        370984223

Upvotes: 1

Views: 431

Answers (2)

gotqn
gotqn

Reputation: 43636

You should remove the GO statements in order to make the whole transaction to be rollback:

IF OBJECT_ID('dbo.t1', 'U') IS NOT NULL 
    DROP TABLE dbo.t1
IF OBJECT_ID('dbo.t2', 'U') IS NOT NULL 
    DROP TABLE dbo.t2
GO

SELECT OBJECT_ID(N't1', N'U') AS t1_Exists, OBJECT_ID(N't2', N'U') AS t2_Exists
GO
   
BEGIN TRANSACTION 

CREATE TABLE t1 (id INT)
CREATE TABLE t1 (id INT) -- Causes an error, since t1 already exists
CREATE TABLE t2 (id INT)

COMMIT TRANSACTION 

GO

SELECT OBJECT_ID(N't1', N'U') AS t1_Exists, OBJECT_ID(N't2', N'U') AS t2_Exist

Upvotes: 3

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

When you put a "Go" Statement inside your code each statement between each Go is considered as a batch and executed separately even if they are in the same file. So In order to avoid this, you should remove all the go statements between the Begin and Commit Transaction. Also Before you commit. Make sure whether there is an active transaction in the session in order to make it safer. Like this 

IF OBJECT_ID('dbo.t1', 'U') IS NOT NULL 
DROP TABLE dbo.t1
IF OBJECT_ID('dbo.t2', 'U') IS NOT NULL 
DROP TABLE dbo.t2
GO

SELECT OBJECT_ID(N't1', N'U') AS t1_Exists, OBJECT_ID(N't2', N'U') AS t2_Exists
GO



BEGIN TRANSACTION 

CREATE TABLE t1 (id INT)


CREATE TABLE t1 (id INT)--Causes an error, since t1 already exists


CREATE TABLE t2 (id INT)

IF @@TRANCOUNT>0    
COMMIT TRANSACTION 



GO

SELECT OBJECT_ID(N't1', N'U') AS t1_Exists, OBJECT_ID(N't2', N'U') AS t2_Exist

Upvotes: 0

Related Questions