June 12, 2018 at 9:31 am
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.
June 12, 2018 at 9:45 am
It sounds like you just need some kind of error trapping in your power shell script, it's a scripting language so it'll happily continue after an error. Try taking a look at the $error built in variable, it'll tell you if the move-item command failed as well as what the exact error message was and if you dig into it more details than what was shown in the error message.
You can also take a look at using try catch blocks, which are also slightly different than in a compiled language. Move-Item I believe will throw a soft error so to actually catch it you have to make sure you have $ErrorActionPreference = "Stop"
June 12, 2018 at 9:56 am
ZZartin - Tuesday, June 12, 2018 9:45 AMIt sounds like you just need some kind of error trapping in your power shell script, it's a scripting language so it'll happily continue after an error. Try taking a look at the $error built in variable, it'll tell you if the move-item command failed as well as what the exact error message was and if you dig into it more details than what was shown in the error message.You can also take a look at using try catch blocks, which are also slightly different than in a compiled language. Move-Item I believe will throw a soft error so to actually catch it you have to make sure you have $ErrorActionPreference = "Stop"
So, that sounds like I can easily identify if the IO operation fails, then stop the SQL commands running.
What if the File Move competes but then the SQL commands fail, how do I then move the file back, what if it was a delete operation instead for example?
June 12, 2018 at 10:36 am
planetmatt - Tuesday, June 12, 2018 9:56 AMZZartin - Tuesday, June 12, 2018 9:45 AMIt sounds like you just need some kind of error trapping in your power shell script, it's a scripting language so it'll happily continue after an error. Try taking a look at the $error built in variable, it'll tell you if the move-item command failed as well as what the exact error message was and if you dig into it more details than what was shown in the error message.You can also take a look at using try catch blocks, which are also slightly different than in a compiled language. Move-Item I believe will throw a soft error so to actually catch it you have to make sure you have $ErrorActionPreference = "Stop"
So, that sounds like I can easily identify if the IO operation fails, then stop the SQL commands running.
What if the File Move competes but then the SQL commands fail, how do I then move the file back, what if it was a delete operation instead for example?
That's a little more involved on the error trapping side, but an easy way to handle that might be to just copy the file instead of moving it and then delete it after the operation finishes.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply