Powershell Transactions

Welcome Forums General PowerShell Q&A Powershell Transactions

This topic contains 10 replies, has 3 voices, and was last updated by

 
Participant
6 months ago.

  • Author
    Posts
  • #102332

    Participant
    Points: 0
    Rank: Member

    I have a Powershell script that gets a list of files from a SQL PROC.

    For each file, it then moves a file, then updates a DB record, then inserts a DB record.

    How can I encapsulate the whole process (IO File Operation, plus the two SQL operations inside a Powershell transaction?

    So if the file move fails, the subsequent SQL UPDATE and INSERT don't happen, and if either the UPDATE or INSERT fail, the preceding file move gets rolled back?

    I'm familiar with pure SQL transactions but I've pretty new to Powershell.

    I'm using the Move-Item cmdlet to do the file operation which I believe supports Powershell transactions, and I'm using System.Data.SQLClient.SQLCommand to access SQL, although I was and could go back to using Invoke-SQLCmd if needed.

  • #102337

    Keymaster
    Points: 1,704
    Helping HandTeam Member
    Rank: Community Hero

    There's nothing native that's capable of that.

  • #102341

    Keymaster
    Points: 1,704
    Helping HandTeam Member
    Rank: Community Hero

    And to expound a bit – transactions in the file system were never fully implemented; only the registry is fully supported. But SQL doesn't support PowerShell transactions, so there's no way to "combine" a SQL transaction (which exists on the SQL Server) with a PowerShell transaction (which is local to the shell instance).

    • #102358

      Participant
      Points: 0
      Rank: Member

      When you say, transaction are not implemented for file operations, why does the MS documentation for Move-Item contain a UseTransaction switch?

  • #102361

    Keymaster
    Points: 1,704
    Helping HandTeam Member
    Rank: Community Hero

    Because all the -Item commands work in all PSProviders. Not every PSProvider implements every feature; run Get-PSProvider to see those which support transactions.

    It's just like some don't support -Filter.

    • #102362

      Participant
      Points: 0
      Rank: Member

      Ok, I understand, thank you. I didn't realise, Move-Item could be used for non file based operations.

  • #102365

    Participant
    Points: 324
    Helping Hand
    Rank: Contributor

    You can build logic to do what you want. This is pseudo-code as an example:

    $files = Invoke-Sqlcmd -Query 'Select * From MyFiles'
    
    foreach ($file in $files) {
        try {
            #Attempt 
            Move-Item -Path $file -Destination 'C:\Windows\Temp' -ErrorAction Stop
    
            try {
                #Write to DB
                Invoke-Sqlcmd ... -ErrorAction Stop
            }
            catch {
                #Write to db, move back
                Move-Item -Path 'C:\Windows\Temp\File1.txt' -ErrorAction Stop
            }
        }
        catch {
            #File move failed
        }
    }
    
    • #102380

      Participant
      Points: 0
      Rank: Member

      Thank you, that's very useful.

      For each of the -ErrorActions, is it possible to trap the specific error message into a PS variable so I can then write some kind of audit row to record when a critical stop for either the file move, or SQL op fails?

  • #102400

    Participant
    Points: 324
    Helping Hand
    Rank: Contributor

    Yes, you can catch specific errors and perform different actions. There is a free ebook above called The Big Book of PowerShell Error Handling that will show you how to do most error handling in Powershell. Another good resource: https://kevinmarquette.github.io/2017-04-10-Powershell-exceptions-everything-you-ever-wanted-to-know/

    Specifically, you are looking for catching typed exceptions and there is a ton of resources on the interwebs. Using try\catch and nesting, you should be able to do any kind of logic you want.

  • #102407

    Participant
    Points: 0
    Rank: Member

    So I need to do a -ErrorAction Stop after the Move-Item because I understand that Cmdlet only soft errors, and will continue on error otherwise.

    Do I need to do anything special to my DB calls that I'm doing like this, or will any error hit the Catch of the Try/Catch?

    $SQLQuery = "INSERT INTO dbo.Log (Col1,Col2) SELECT '$Val1', '$Val2'"

    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $SQLFileAuditInsertQuery
    $SqlCmd.Connection = $SqlConnection
    $SqlCmd.CommandTimeout = $SQLTimeout
    $SqlCmd.ExecuteNonQuery()
    $SqlConnection.Close()

  • #102412

    Participant
    Points: 324
    Helping Hand
    Rank: Contributor

    Will this process run from a single place? Two options:

    1. Put SQLPS Powershell Module on the box. This will make a pre-requisite for the script, but rather than executing SQL with a lot of code, you can leverage the Invoke-SqlCmd cmdlet. https://docs.microsoft.com/en-us/sql/powershell/download-sql-server-ps-module?view=sql-server-2017
    2. Place all of your SQL code into a function. You will need to catch errors in several places, like the connection and the Execute lines, so if you're going to call in multiple places, you definitely want to use a function. If you don't know or control where the code will execute to put a pre-requisite module, then leverage a function.

    If you contain your SQL call in Invoke-SQLCmd or a custom function, then you can have a single try\catch around those calls and keep your code clean.

The topic ‘Powershell Transactions’ is closed to new replies.