Run Script

RunScript.sql

SET @scriptName = '$(fileName)'

DECLARE @count int
select @count = count(Name) from Script where Name = @scriptName and RanOk = 1

IF (@count = 0)
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION 

        r: "$(fileName)"

        COMMIT TRANSACTION
        
        INSERT INTO [dbo].[Script] ([Name], [Date], [RanOk], [Observation])
        VALUES (@scriptName, GETDATE(), 1, NULL)

        PRINT 'SCRIPT [' + @scriptName +'] COMPLETED SUCCESSFULLY'
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 
            ROLLBACK TRANSACTION

        DECLARE @error nvarchar(255)
        set @error = ERROR_MESSAGE()

        INSERT INTO [dbo].[Script] ([Name], [Date], [RanOk], [Observation])
        VALUES (@scriptName, GETDATE(), 0, @error)

        PRINT 'SCRIPT [' + @scriptName +'] FAILED'
        PRINT 'Error:' + @error 
    END CATCH
END
ELSE
BEGIN
    PRINT 'SCRIPT [' + @scriptName +'] WAS PREVIUSLY RUN'
END