CODEWITHSUNDEEP
sqlt-sqltransactionsmigration
CR500
CR500

Reputation: 41

Running a script inside a transaction T-SQL

Is it possible to run a script inside a transaction? Then I could just edit the path to the script and run them in a secure way.

:setvar ScriptPath "C:\Script1.sql"
BEGIN TRY
    BEGIN TRANSACTION
        :r $(ScriptPath)
    COMMIT TRANSACTION
    PRINT 'Successfull'
END TRY
BEGIN CATCH
    PRINT 'Error'
    ROLLBACK TRANSACTION
END CATCH

But I get the following errors as soon as I try running a script in the transaction: Incorrect syntax near 'BEGIN'. Incorrect syntax near 'TRY'. Incorrect syntax near 'CATCH'.

Upvotes: 0

Views: 2001

Answers (1)

SAS
SAS

Reputation: 4035

Maybe wrong SQL version - what version are you on?

Could be something wrong in your include script contents.

(Also, make sure SQLCMD mode is enabled.)

The following works (selects 1):

:setvar ScriptPath "C:\temp\Script1.sql"
BEGIN TRY
    BEGIN TRANSACTION
         :r $(ScriptPath)
    COMMIT TRANSACTION
    PRINT 'Successfull'
END TRY
BEGIN CATCH
    PRINT 'Error'
    ROLLBACK TRANSACTION
END CATCH

File C:\temp\Script1.sql contains "SELECT 1"

Upvotes: 1

Related Questions