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:

Truncating a Temp Table I'd created earlier in the script.
Bulk inserting the Logs from the CSV file into the Temp Table.
Inserting the Message Tracking rows into a Message Tracking table from the Temp Table
Inserting the Recipient info into a Recipients table from the Temp Table.
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!

score 2 · Accepted Answer · answered May 19 '13 at 14:29

2

Consider using xact_abort:

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

answered May 19 '13 at 14:29

Andomar

232,371
49
380
404

I added that and it seems to do the trick thanks! I inserted a Divide by Zero on purpose in the middle of the statement and it didn't apply the previous changes, and allowed me to catch the Exception in PowerShell too. 2013-05-19 16:50:53 - Divide by zero error encountered. At C:\Path\To\Script\ScriptName.ps1:87 char:26 + $Command.ExecuteNonQuery <<<< () | Out-Null + CategoryInfo : NotSpecified: (:) [], SqlException + FullyQualifiedErrorId : DotNetMethodException – HungryHippos May 19 '13 at 16:28
Shouldn't there be an error check at the end of the SQL statement & an if statement to issue a `rollback` if there are errors, otherwise `commit`? – alroc May 19 '13 at 17:52
@alroc: That's what `XACT_ABORT` does. If there are errors, it rolls back the transaction. – Andomar May 19 '13 at 18:28
I guess if it doesn't get committed and the connection is then disconnected it doesn't need to do so? I ran DBCC OPENTRAN against the Database and it came back with nothing even after it did run the BEGIN TRANSACTION and subsequently had the Exception occur. – HungryHippos May 19 '13 at 18:29

Initiate Rollback if any SQL command fails

1 Answers1