PowerShell - How to Commit Scope if all Invoke-SQL Commands are successful

user_8534089 0
Viewed 700

I am using PowerShell version 5, And I am executing a couple of SQL commands in PowerShell script. Unlike SQL Server where we execute commands in BEGIN TRANSACTION and COMMIT TRANSACTION so that if a single command fails everything is rollbacked I wanted to achieve a similar thing in my Powershell script.

So in my scenario, I have 3 SQL commands if any fails then the table should not be dropped:

  • 1st DROP TABLE IF EXISTS
  • 2nd Create table
  • 3rd Insert Records

While inserting records I am trying to insert multiple rows using an array and to test the working of scope so I am intentionally adding duplicate Id value, so the expected result is if Id i.e. 1st array element is valid then proceed for next element, but if 2nd array element is not valid then rollback changes.

But since I have limited knowledge of Powershell so not sure how to achieve this as a result session is not getting committed even if Id's are not duplicate or duplicate.

Below is my script so far:

$connString = "Data Source=<Server-Name>;Database=<DB-Name>;User ID=<Login>;Password=<Pass>"
 
 
  $conn = New-Object System.Data.SqlClient.SqlConnection $connString
  $arr_values = @('1','1') 
  $tbl_name = "dbo.test1"
 
  $conn.Open()
  try
  {
    if($conn.State -eq "Open")
    {         
     foreach ($Id in $arr_values) 
     {
        $scope = New-Object -TypeName System.Transactions.TransactionScope
       
        $Drop_Command= "DROP Table IF EXISTS $tbl_name"
        Invoke-Sqlcmd -ServerInstance <Server-Name> -Database <DB-Name> -Query $Drop_Command
        
        $Create_Command = "Create table dbo.test1 (Id int, CONSTRAINT PK_test1_Id PRIMARY KEY (Id))"
        Invoke-Sqlcmd -ServerInstance <Server-Name> -Database <DB-Name> -Query $Create_Command
        
        
        $Insert_Command = "INSERT INTO dbo.test1(Id) Values ($Id)"
        Write-Host "$Insert_Command->" $Insert_Command
        Invoke-Sqlcmd -ServerInstance <Server-Name> -Database <DB-Name> -Query $Insert_Command
        # Start-Sleep -Seconds 10
        
        Write-Host "Record Inserted with Id->" $Id
     }
     $scope.Complete()
     # $scope.Dispose()     
   }            
 }     
 catch
 {
      Write-Host "Record not Inserted ->" $Id
      $_.exception.message
 }
 finally
 {
    $scope.Dispose() 
    $conn.Close()
 }
powershell-5.0 powershell sql-server
3 Answers

Invoke-SqlCmd is a convenience method. Either build a single script with all the transaction handling in it and pass that to Invoke-SqlCmd, or use SqlConnection, SqlCommand, SqlTransaction objects directly.

And I'm not sure TransactionScope works in PowerShell.

user_72977000

Adding to David's answer, you can use SqlTransaction and SqlCommand objects instead of Invoke-SqlCmd. Here's some example code based on the code in your question. I did not attempt to correct errors in the original code (e.g. the table dbo.test1 create will fail on the second iteration).

$connString = "Data Source=<Server-Name>;Database=<DB-Name>;User ID=<Login>;Password=<Pass>"
 
$conn = New-Object System.Data.SqlClient.SqlConnection $connString
$arr_values = @('1','1') 
$tbl_name = "dbo.test1"
 
$conn.Open()
$tran = $conn.BeginTransaction()

try {

    foreach ($Id in $arr_values) {

        $script = @"
            DROP Table IF EXISTS $tbl_name;
            Create table dbo.test1 (Id int, CONSTRAINT PK_test1_Id PRIMARY KEY (Id));
            INSERT INTO dbo.test1(Id) Values ($Id);
"@

        $cmd = New-Object System.Data.SqlClient.SqlCommand($script, $conn)
        $cmd.Transaction = $tran
        [void]$cmd.ExecuteNonQuery()
        Write-Host "Record Inserted with Id->" $Id
        }
    $tran.Commit()

}    

catch {
    $tran.Rollback()
    Write-Host "Record not Inserted ->" $Id
    $_.exception.message
}
finally {
    $conn.Close()
}
user_37111620

I have no knowledge on PowerShell, but it seems you try to solve the issue in a wrong way.

With SQL there are 2 sets of commands: DDL - data definition language and DML - data manupulation language.

Transactions, if they are supported by db engine, work correctly with DML. To support transactions with DDL, you should not rely on it. It is a bit of theory.

What you could do - reorder and change a bit, to guarantee what you want:

  1. Create a table with another name
  2. Try to insert data. If it fails, drop that table.
  3. If insert is successfull, drop the original table and rename the new table to the original one.

The previous solution makes sense only if the tables structure is different. If tables are the same, just:

  1. Begin transaction
  2. Delete everything.
  3. Insert
  4. Commit

If insert is sucessfull, original data in the first table is deleted. If not, commit is not applied. No reason to drop table as it is.

user_5118040
Related