how to execute multiple Invoke-Sqlcmd in one transaction?

Question

I would like to perform a bunch of invoke-sqlcmd in one sql transaction. Here's what I'm doing:

try{
   $scope = New-Object -TypeName System.Transactions.TransactionScope

   GetFiles $SqlFilesDirectory

   $scope.Complete() 
}
catch{
    $_.exception.message
}
finally{
    $scope.Dispose() 
}

Here's how GetFiles is defined:

    #
# Get SQL Files recursively
#
function GetFiles($path = $pwd) 
{ 
    $subFolders = Get-ChildItem -Path $path -Directory  | Select-Object FullName,Name | Sort-Object -Property Name 
    $sqlFiles = Get-ChildItem -Path $path -Filter *.sql | Select-Object FullName,Name | Sort-Object -Property Name
    
    foreach ($file in $sqlFiles)
    {
      Write-Host "file: " $file.Name
      Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $DBName -Username $SvcAdminAccount -Password $SvcAdminPassword -InputFile $file.FullName -QueryTimeout 65535
    } 

    foreach ($folder in $subFolders)
    {
       Write-Host "`nGetting files for subfolder: " $folder.Name
       GetFiles $folder.FullName
    } 
}

How do we perform a series of invoke-sqlcmd in one transaction?

Here's the output:

The behavior that I want is that ALL changes are rolled back if a single sql script fails.

Maybe you can concat the scripts into 1 script, with GO commands in between: `file-1\r\nGO\r\nfile-2...\r\nGO\r\nfile-x` and run that? — Peter B, Oct 27 '21 at 14:48
`Invoke-SqlCmd` executes under a different scope, so it won't use that transaction. Use `SqlConnection` and `SqlCommand` instead — Charlieface, Oct 27 '21 at 15:30
@Charlieface, `SqlConnection` and `SqlCommand` are not PowerShell commands. From where do these commands come? — lit, Oct 27 '21 at 15:41
See https://stackoverflow.com/a/18935548/14868997 for example. But make sure you use `try/finally` blocks, that code doesn't have it. — Charlieface, Oct 27 '21 at 15:45
you can directly build .NET objects from PowerShell. `[System.Data.SqlClient.SqlConnection]`. PowerShell cmdlets return .NET objects and format the output. But you can directly create a `[System.Data.SqlClient.SqlConnection]`, a `[System.Data.SqlClient.SqlCommand]` and use a `[System.Data.SqlClient.SqlTransaction]`. In such case the SQL cmdlets are not really useful. — Hazrelle, Oct 27 '21 at 15:46
according to this https://stackoverflow.com/questions/13977650/multiple-invoke-sqlcmd-and-sql-server-transaction there shouldnt be any issues with my code — Alex Gordon, Oct 27 '21 at 21:43

how to execute multiple Invoke-Sqlcmd in one transaction?

0 Answers0