1

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.

  • It would be better to see the code in this section "SOME CODE REGARDING MOVEMENT OF DATE FROM ORACLE TO SQL SERVER". And what is the point of this block: begin transaction and then commit it right away? – George K Sep 30 '19 at 6:43
2
  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

  • Thanks for ur reply. Regarding ERROR 3902, is it possible that such error happens when i use commit without BEGIN TRANSACTION? I have added COMMIT after 2 insert statements. But there is no BEGIN TRANSACTION stmt for those commit statement. Is this the reason y i am getting such an error? – Saivishnu S R Sep 30 '19 at 9:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.