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.

file-1\r\nGO\r\nfile-2...\r\nGO\r\nfile-xand run that?Invoke-SqlCmdexecutes under a different scope, so it won't use that transaction. UseSqlConnectionandSqlCommandinsteadSqlConnectionandSqlCommandare not PowerShell commands. From where do these commands come?try/finallyblocks, that code doesn't have it.[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.