Initiate Rollback if any SQL command fails

Question

I'm looking at updating an old PowerShell script I ran for inserting Exchange Message Tracking logs into SQL. Due to the way I am pulling the Message Tracking logs into a tab delimited CSV file and then importing them, I have quite a big SQL statement that I'd like to Rollback and throw an exception in case of errors or problems...

I am doing this in the following order:

1. Truncating a Temp Table I'd created earlier in the script.
2. Bulk inserting the Logs from the CSV file into the Temp Table.
3. Inserting the Message Tracking rows into a Message Tracking table from the Temp Table
4. Inserting the Recipient info into a Recipients table from the Temp Table.
5. Updating my Servers table to reflect the fact that I've successfully stepped forwards in time and inserted the logs.

Below is my function from PowerShell, should be easy to see what is running even if you don't know PowerShell:

Function Import-TrackingLogs
{
    $Command = New-Object System.Data.SQLClient.SQLCommand
    $Command.Connection = $Connection
    $Command.CommandTimeOut = 2000
    $Command.CommandText = "
    TRUNCATE TABLE $TempTable1;

    BULK INSERT $TempTable1
    FROM '$ExportTrackingFile'
    WITH
    (
        FIRSTROW = 2,
        FIELDTERMINATOR = '\t'
    );

    INSERT INTO E12_MessageTracking
    SELECT
    CASE LEN([TimeStamp]) WHEN 2 THEN NULL ELSE REPLACE([TimeStamp], '" + [char]34 + "', '') END AS [TimeStamp],
    CASE LEN([Sender]) WHEN 2 THEN NULL ELSE REPLACE([Sender], '" + [char]34 + "', '') END AS [Sender],
    CASE LEN([Recipients]) WHEN 2 THEN NULL ELSE REPLACE([Recipients], '" + [char]34 + "', '') END AS [Recipients],
    CASE LEN([MessageSubject]) WHEN 2 THEN NULL ELSE REPLACE([MessageSubject], '" + [char]34 + "', '') END AS [MessageSubject],
    CASE LEN([EventId]) WHEN 2 THEN NULL ELSE REPLACE([EventId], '" + [char]34 + "', '') END AS [EventId],
    CASE LEN([Source]) WHEN 2 THEN NULL ELSE REPLACE([Source], '" + [char]34 + "', '') END AS [Source],
    CASE LEN([MessageId]) WHEN 2 THEN NULL ELSE REPLACE([MessageId], '" + [char]34 + "', '') END AS [MessageId],
    CASE LEN([InternalMessageId]) WHEN 2 THEN NULL ELSE REPLACE([InternalMessageId], '" + [char]34 + "', '') END AS [InternalMessageId],
    CASE LEN([RecordGuid]) WHEN 2 THEN NULL ELSE REPLACE([RecordGuid], '" + [char]34 + "', '') END AS [RecordGuid],
    CASE LEN([ClientIp]) WHEN 2 THEN NULL ELSE REPLACE([ClientIp], '" + [char]34 + "', '') END AS [ClientIp],
    CASE LEN([ServerHostname]) WHEN 2 THEN NULL ELSE REPLACE([ServerHostname], '" + [char]34 + "', '') END AS [ServerHostname],
    CASE LEN([ConnectorId]) WHEN 2 THEN NULL ELSE REPLACE([ConnectorId], '" + [char]34 + "', '') END AS [ConnectorId],
    CASE LEN([RecipientStatus]) WHEN 2 THEN NULL ELSE REPLACE([RecipientStatus], '" + [char]34 + "', '') END AS [RecipientStatus],
    CASE LEN([RecipientCount]) WHEN 2 THEN NULL ELSE REPLACE([RecipientCount], '" + [char]34 + "', '') END AS [RecipientCount],
    CASE LEN([TotalBytes]) WHEN 2 THEN NULL ELSE REPLACE([TotalBytes], '" + [char]34 + "', '') END AS [TotalBytes],
    CASE LEN([FromServer]) WHEN 2 THEN NULL ELSE REPLACE([FromServer], '" + [char]34 + "', '') END AS [FromServer]
    FROM $TempTable1;

    INSERT INTO E12_MessageTracking_Recipients
    SELECT
    SUBSTRING (s.[TimeStamp], 2, LEN(s.[TimeStamp]) -2) AS [TimeStamp],
    CASE LEN(s.[MessageId]) WHEN 2 THEN NULL ELSE (SUBSTRING (s.[MessageId], 2, LEN(s.[MessageId]) -2)) END AS [MessageId],
    CASE LEN(s.[InternalMessageId]) WHEN 2 THEN NULL ELSE (SUBSTRING (s.[InternalMessageId], 2, LEN(s.[InternalMessageId]) -2)) END AS [InternalMessageId],
    CASE LEN(s.[RecordGuid]) WHEN 2 THEN NULL ELSE (SUBSTRING (s.[RecordGuid], 2, LEN(s.[RecordGuid]) -2)) END AS [RecordGuid],
    f.Value AS [Recipient]
    FROM #E12_MessageTracking AS s
    CROSS APPLY dbo.SplitStrings(SUBSTRING(s.[Recipients], 2, LEN(s.[Recipients]) -2), '|') AS f;

    UPDATE E12_MessageTracking_Servers
    SET LogUpdatedTime = '$EndTime';"

    $Command.ExecuteNonQuery() | Out-Null
}

I could wrap each individual command into a Powershell try/catch block, but figured it might be better to see if SQL can handle this for me.

This was my previous rollback in PowerShell:

$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandTimeOut = 30
$Command.CommandText = "
DELETE FROM E12_MessageTracking WHERE TimeStamp >= '$StartTime';
DELETE FROM E12_MessageTracking_Recipients WHERE TimeStamp >= '$StartTime';
UPDATE E12_MessageTracking_Servers
SET LogUpdatedTime = '$StartTime';"

$Command.ExecuteNonQuery() | Out-Null

I was looking at this page SQL Server - transactions roll back on error?, but unsure how I would wrap that into my commands as I'm not just running one thing, but multiple commands.

Thanks!

Answer 1

Consider using xact_abort:

set xact_abort on
begin transaction
<your big sql statement here>
commit transaction