mardi 5 mai 2015

SQL script won't auto commit

Just want to know if the following is the correct way to write SQL script that uses BEGIN TRANSACTION? (something that works like DML)

BEGIN TRY
    BEGIN TRANSACTION
        /* SQL statements here */
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF @@ERROR <> 0
    ROLLBACK TRANSACTION
END CATCH

Whenever i tried execute it in SQL server, the script ran but somehow it won't commit the transaction. I still have to manually commit with the COMMIT TRANSACTION.

In my SQL Server Management Studio 2012, i did checked the SET_IMPLICIT_TRANSACTIONS to avoid autocommit when developing the script. But since i already put the statement COMMIT TRANSACTION if all statements ran successfully, then by right my script should autocommit right? Unless if i got mistaken here.

Aucun commentaire:

Enregistrer un commentaire