sql server - Initiate Rollback if any SQL command fails -

i'm looking @ updating old powershell script ran inserting exchange message tracking logs sql. due way pulling message tracking logs tab delimited csv file , importing them, have quite big sql statement i'd rollback , throw exception in case of errors or problems...

i doing in following order:

1. truncating temp table i'd created earlier in script.
2. bulk inserting logs csv file temp table.
3. inserting message tracking rows message tracking table temp table
4. inserting recipient info recipients table temp table.
5. updating servers table reflect fact i've stepped forwards in time , inserted logs.

below function powershell, should easy see running if 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     '$exporttrackingfile'         (         firstrow = 2,         fieldterminator = '\t'     );      insert e12_messagetracking     select     case len([timestamp]) when 2 null else replace([timestamp], '" + [char]34 + "', '') end [timestamp],     case len([sender]) when 2 null else replace([sender], '" + [char]34 + "', '') end [sender],     case len([recipients]) when 2 null else replace([recipients], '" + [char]34 + "', '') end [recipients],     case len([messagesubject]) when 2 null else replace([messagesubject], '" + [char]34 + "', '') end [messagesubject],     case len([eventid]) when 2 null else replace([eventid], '" + [char]34 + "', '') end [eventid],     case len([source]) when 2 null else replace([source], '" + [char]34 + "', '') end [source],     case len([messageid]) when 2 null else replace([messageid], '" + [char]34 + "', '') end [messageid],     case len([internalmessageid]) when 2 null else replace([internalmessageid], '" + [char]34 + "', '') end [internalmessageid],     case len([recordguid]) when 2 null else replace([recordguid], '" + [char]34 + "', '') end [recordguid],     case len([clientip]) when 2 null else replace([clientip], '" + [char]34 + "', '') end [clientip],     case len([serverhostname]) when 2 null else replace([serverhostname], '" + [char]34 + "', '') end [serverhostname],     case len([connectorid]) when 2 null else replace([connectorid], '" + [char]34 + "', '') end [connectorid],     case len([recipientstatus]) when 2 null else replace([recipientstatus], '" + [char]34 + "', '') end [recipientstatus],     case len([recipientcount]) when 2 null else replace([recipientcount], '" + [char]34 + "', '') end [recipientcount],     case len([totalbytes]) when 2 null else replace([totalbytes], '" + [char]34 + "', '') end [totalbytes],     case len([fromserver]) when 2 null else replace([fromserver], '" + [char]34 + "', '') end [fromserver]     $temptable1;      insert e12_messagetracking_recipients     select     substring (s.[timestamp], 2, len(s.[timestamp]) -2) [timestamp],     case len(s.[messageid]) when 2 null else (substring (s.[messageid], 2, len(s.[messageid]) -2)) end [messageid],     case len(s.[internalmessageid]) when 2 null else (substring (s.[internalmessageid], 2, len(s.[internalmessageid]) -2)) end [internalmessageid],     case len(s.[recordguid]) when 2 null else (substring (s.[recordguid], 2, len(s.[recordguid]) -2)) end [recordguid],     f.value [recipient]     #e12_messagetracking s     cross apply dbo.splitstrings(substring(s.[recipients], 2, len(s.[recipients]) -2), '|') f;      update e12_messagetracking_servers     set logupdatedtime = '$endtime';"      $command.executenonquery() | out-null } 

i wrap each individual command powershell try/catch block, figured might better see if sql can handle me.

this previous rollback in powershell:

$command = new-object system.data.sqlclient.sqlcommand $command.connection = $connection $command.commandtimeout = 30 $command.commandtext = " delete e12_messagetracking timestamp >= '$starttime'; delete e12_messagetracking_recipients timestamp >= '$starttime'; update e12_messagetracking_servers set logupdatedtime = '$starttime';"  $command.executenonquery() | out-null 

i looking @ page sql server - transactions roll on error?, unsure how wrap commands i'm not running 1 thing, multiple commands.

thanks!

consider using xact_abort:

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