0

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:

enter image description here

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

6
  • 1
    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? Commented Oct 27, 2021 at 14:48
  • 3
    Invoke-SqlCmd executes under a different scope, so it won't use that transaction. Use SqlConnection and SqlCommand instead Commented Oct 27, 2021 at 15:30
  • @Charlieface, SqlConnection and SqlCommand are not PowerShell commands. From where do these commands come? Commented Oct 27, 2021 at 15:41
  • See stackoverflow.com/a/18935548/14868997 for example. But make sure you use try/finally blocks, that code doesn't have it. Commented Oct 27, 2021 at 15:45
  • 1
    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. Commented Oct 27, 2021 at 15:46

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.