SQL Server TSQL – Begin and GO

sql serversql-server-2012

i have a TSQL code to move DATA from ORACLE to SQL SERVER. It is scheduled to run daily. But, for some reason it is not working as expected like taking a long time to finish execution and getting error like ERROR 3902 COMMIT TRANSACTION request has no Corresponding BEGIN TRANSACTION. The code was tested on the local server and was working properly.
I have some doubts regarding certain parts of the code.

SOME CODE REGARDING MOVEMENT OF DATE FROM ORACLE TO SQL SERVER    
GO
    begin transaction
    commit;
    go

My doubts are as follows:

  1. I have read that GO is not a TSQL command. So, does it work in a scheduler?
  2. Begin TRANSACTION should start a transaction explicitly. But there is no code after BEGIN TRANSACTION. So how does it understand the above code should be executed?

I have solved the issue. Issue was with commit statement. After, every insert statement i added a commit(similar to pl/sql). I added begin transaction before every commit and it worked. I think commit works differently in pl/sql and TSQL.

Best Answer

  1. GO is not a Transact-SQL statement, instead it is a command recognized by the Sql Server Management Studio (i.e. SSMS), SQLCMD and OSQL utilities. These utilities send all statements after the previous GO statement and before the current GO statement as one Batch to the Sql Server engine for execution. It should work in a scheduler
  2. It looks like your transaction failed, got rolled back and there is nothing to commit.

This will NOT run

CREATE TABLE Test
(
    id INT PRIMARY KEY NOT NULL
);
GO


BEGIN TRAN;
INSERT Test
VALUES
('a');
GO
COMMIT TRAN;

Msg 245, Level 16, State 1, Line 6 Conversion failed when converting the varchar value 'a' to data type int. Msg 3902, Level 16, State 1, Line 9 The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

This will run without a problem

CREATE TABLE Test
(
    id INT PRIMARY KEY NOT NULL
);
GO

BEGIN TRAN;
INSERT Test
VALUES
(5);
GO

COMMIT TRAN;

A good article on transactions http://www.sommarskog.se/error_handling/Part1.html

Related Question