title: SQL Queries as a Transaction in Powershell
body: <p>I am looking to execute a few SQL queries as a transaction in PowerShell. However, I am getting errors and could not find a suitable answer.
  Besides the error, is BeginTransaction() and CommitTransaction() the way to perform transaction in Powershell? Intellisense doesn't seem to show CommitTransaction() method.</p>
  
  <p>Error:</p>
  
  <pre><code>Exception calling "ExecuteNonQuery" with "0" argument(s): "ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction.  The Transaction property of the command has not been 
  initialized
  </code></pre>
  
  <p>Code:</p>
  
  <pre><code>try {
      #SQL Connection
      $Connection = New-Object System.Data.SQLClient.SQLConnection
      $Connection.ConnectionString = "Server=$server,1433;Initial Catalog=$Database;Persist Security Info=False;User ID=$UserID;Password=$Password;MultipleActiveResultSets=true;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;;"
  
      $Connection.Open()
  
      $Command = New-Object System.Data.SQLClient.SQLCommand
      $Command.Connection = $Connection
  
      #Begin Transaction
      $Connection.BeginTransaction()
  
      #INSERT into PROJECT table 
      $input_2 = $projectName
      $input_3 = $blobUri + $configFileName
      $input_4 = $key
      $input_5 = $projectType
      $input_6 = $projectIsActive
  
      $sql =
      "
      begin 
          INSERT INTO PROJECT 
          ([PROJECT_NAME],[CONFIGURATION_LOCATION],[ENCRYPTION_KEY],[PROJECT_TYPE],[IS_ACTIVE],[CREATED_DATETIME])
          select '$input_2', '$input_3', '$input_4', '$input_5', '$input_6', cast(CONVERT(datetimeoffset,GETDATE()) AT TIME ZONE 'AUS Eastern Standard Time' as datetime)
      end
      " 
      $Command.CommandText = $sql
      $Command.ExecuteNonQuery()
  
      #Get Project ID from PROJECT table
      $sql =
      "
      begin 
          SELECT TOP 1 PROJECT_ID FROM PROJECT WHERE PROJECT_NAME='$projectName'
      end
      " 
      $Command.CommandText = $sql
      $projectID = $Command.ExecuteScalar() 
  
  
      #Commit Transaction
      $Connection.CommitTrasaction()
  
      #Close Connection
      $Connection.Close()
  }
  Catch {
      throw   
  
  }
  </code></pre>