Sample Header Ad - 728x90

Can I do transaction for dynamic sql which contains DISABLE change tracking / TRUNCATE / PARTITION SWITCH

0 votes
1 answer
201 views
I would like to create transaction which will : 1. DISABLE change tracking IF exists. 2. TRUNCATE partition for table. 3. SWITCH PARTITION from different table to main table. 4. ENABLE change tracking IF was disabled before. Would like to create script with parameters so I will be using dynamic SQL. Idea:
/* OPS parameters */
DECLARE @schemaName sysname = 'dbo';
DECLARE @tableName sysname = 'TABLE';
DECLARE @partition INT = 90;  

/* DEV parameters */
DECLARE @tableNameSRP sysname = CONCAT(@tableName, '_SRP');
DECLARE @tableNameWithSchema sysname = CONCAT(QUOTENAME(@schemaName), '.', QUOTENAME(@tableName));
DECLARE @tableNameWithSchemaSRP sysname = CONCAT(QUOTENAME(@schemaName), '.', QUOTENAME(@tableNameSRP));
DECLARE @isCtReEnabled BIT = 0;
DECLARE @isDebug BIT = 1;


SET TRAN ISOLATION LEVEL READ UNCOMMITTED;
SET XACT_ABORT ON;
BEGIN TRAN;
BEGIN TRY
    IF EXISTS (
        SELECT
            1
        FROM sys.change_tracking_tables
        WHERE object_id = OBJECT_ID(@tableNameWithSchema)
    )
    BEGIN

        SET @statement = N'ALTER TABLE ' + @tableNameWithSchema + N' DISABLE CHANGE_TRACKING;';
        IF (@isDebug = 0)
        BEGIN
            EXEC sp_executesql @stmt = @statement;
        END;
        IF (@isDebug = 1)
        BEGIN
            RAISERROR('[INFO] SQL: %s', 0, 1, @statement) WITH NOWAIT;
        END;
        SET @isCtReEnabled = 1;
    END;

    SET @statement
        = N'TRUNCATE TABLE ' + @tableNameWithSchema + N' WITH (PARTITIONS (' + CAST(@partition AS NVARCHAR(5)) + N')) 
ALTER TABLE '        + @tableNameWithSchemaSRP + N' SWITCH PARTITION ' + CAST(@partition AS NVARCHAR(5)) + N' TO ' + @tableNameWithSchema + N' PARTITION '
          + CAST(@partition AS NVARCHAR(5));
    IF (@isDebug = 0)
    BEGIN
        EXEC sp_executesql @stmt = @statement;
    END;
    IF (@isDebug = 1)
    BEGIN
        RAISERROR('[INFO] SQL: %s', 0, 1, @statement) WITH NOWAIT;
    END;

    IF (@isCtReEnabled = 1)
    BEGIN
        SET @statement = N'ALTER TABLE ' + @tableNameWithSchema + N' ENABLE CHANGE_TRACKING;';
        IF (@isDebug = 0)
        BEGIN
            EXEC sp_executesql @stmt = @statement;
        END;
        IF (@isDebug = 1)
        BEGIN
            RAISERROR('[INFO] SQL: %s', 0, 1, @statement) WITH NOWAIT;
        END;
    END;
    COMMIT;

END TRY
BEGIN CATCH
    SET @errorMessage = ERROR_MESSAGE();
    RAISERROR('ERROR MESSAGE: %s', 0, 1, @errorMessage) WITH NOWAIT;
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;
My questions: 1. Is operation like ENABLE / DISABLE change tracking , TRUNCATE and PARTITION SWITCH will be working in one transaction ? (want to execute all or nothing) 2. Is dynamic sql is a problem in this case ?
Asked by adam.g (465 rep)
Aug 28, 2023, 03:28 PM
Last activity: Aug 28, 2023, 08:35 PM