Updated SQL Queries
Awhile back I wrote the Invoke-SQLQuery script, it’s a light-weight easy way to query a SQL database and get data back. It’s primary appeal over just using Invoke-SQLCMD was there is no need to install anything, just run the script, get your data and go. Easy, peesy. Now it’s time for a big update in capability.
A Little Background
I don’t know about you, but we have a lot of SQL databases–unfortunately we have a lot of SQL servers too, but we’re working on that!–and for some reason management always wants reports coming out of them. Now, I’ve heard about SSRS and I’ve been using Crystal Reports for over a decade so getting reports out is quite well documented but what I really know is PowerShell. Besides, SSRS requires specialized knowledge that no one on our team has and honestly not a ton of interest in learning and Crystal Reports requires some very expensive software.
Since the time I introduced Invoke-SQLQuery to my work it has wormed its way into a lot of scripts. We have faxing reports–I really have to tell you about our faxing some day–SQL backup and job reports, pulling data out of Orion for Graphite charts and a dozen other things. It is safe to say that it really transformed our capabilities as far as data reporting and manipulation. For what it was originally intended for, it really worked well.
But, and I’m sure you can see this coming, eventually I started to run into some limitations. One was the “Message” stream as I call it. Have you ever used the PRINT statement in a SQL query? Great way for getting simple text output. Also when you run a query that effects a lot of rows SQL will respond with how many rows were changed, not necessarily something you want in your report but it would be nice to have it in a log file. Something the original Invoke-SQLQuery simply couldn’t capture. Another problem was there was no error trapping coming out of it. If you had a query that failed the only way to test for that was to assign the output to a variable and then test if that variable was $null. Not an precise method of error capture! This started to become a problem when I was working on a script that would detect all my Availability Groups and fail them over to an alternate node for patching. Well, none of the queries used for the failover ever return any information so how would my script know if it worked or not?
Getting the Message
I’m not going to lie. I’m just not smart enough, nor dumb enough, to do this on my own. I Googled the problem and quickly ran across this article on stackexchange. Gave it a try and low and behold I had PRINT information coming out of my queries. That was easy! Not a big fan of Write-Host, not to mention it’s not really useful for me so I changed it to Write-Verbose and moved on to the next problem. Except that turned out to be the next problem. Write-Verbose didn’t output anything. Even when $VerbosePreference was set to “Continue”. Figuring this was some kind of scoping issue I tried a few things in the event handler and didn’t get anywhere, so it was time to fake it out. Instead of writing verbose I’d have to save the message and then replay it later. Not going to lie, I went through quite a few iterations of this before finally coming up with the winning combination, but I don’t want to bore you with all the different things I tried–besides, it’s embarrassing!
Eventually I came up with this event handler:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| $ErrorHandlerScript = { | |
| Param( | |
| $Sender, | |
| $Event | |
| ) | |
| $Message.Add([PSCustomObject]@{ | |
| Number = $Event.Errors.Number | |
| Line = $Event.Errors.LineNumber | |
| Message = $Event.Errors.Message | |
| }) | Out-Null | |
| } |
This allowed me to capture all of the message data, and any errors that might come out. After that I just had to put a small loop at the end to display the messages after they were done. I also wanted to add the ability to send the “message” stream to StdOut so you could capture it in your PowerShell script, hence the $PrintToStdOut switch in the code here:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # $Message was the variable where I stored the message information | |
| ForEach ($Warning in ($Message | Where Number -eq 0)) | |
| { | |
| If ($PrintToStdOut) | |
| { | |
| Write-Output $Warning.Message | |
| } | |
| Else | |
| { | |
| Write-Verbose $Warning.Message –Verbose | |
| } | |
| } | |
| $Errors = @($Message | Where Number -ne 0) | |
| If ($Errors.Count) | |
| { | |
| ForEach ($MsgError in $Errors) | |
| { | |
| Write-Error "Query Error $($MsgError.Number), Line $($MsgError.Line): $($MsgError.Message)" | |
| } | |
| } |
ErrorActionPreference
I’ve not played much with Error Action Preferences before, and this turned out to be a great way to do it. Turns out they are sinfully easy to handle within your script. Just Write-Error you message out. Yep. That’s it. If $ErrorActionPreference is set to “Continue” (the default) it writes to the error stream and moves on. If it’s set to “SilentlyContinue” then nothing appears on your screen and the script moves on. If it’s set to “Stop” then your script will write out the error and immediately stop, something that can be captured in a Try/Catch block and the error will log in the $Error variable so all of your normal error trapping techniques will work just fine. Sometimes PowerShell just does things to make you fall in love with it all over again.
Anyway, feel free to take a look at the code, since I keep it up on GitHub now:
Invoke-SQLQuery
5 Comments »
Leave a Reply
Me
LinkedIn
The Surly Admin 
[…] functions in it all relating to SQL. The primary function, and if you know me you know this, is Invoke-SQLQuery. But there are several other functions that all work with SQL Availability Groups, Get-AGCluster, […]
[…] Invoke-SQLQuery: This is the backbone of the module, and lets you quickly and easily run a SQL query against one, or many SQL servers. […]
Hi, this is really cool and I have been enjoying your posts. I have to ask any particular reason why you are using “| Out-Null” vs ” > $null”? I recently was doing some research comparing the two and it seems like the “$null” or “void” options are much more performant. Check out this profile someone did on StackOverflow http://stackoverflow.com/questions/5260125/whats-the-better-cleaner-way-to-ignore-output-in-powershell .
Interesting question. In almost all cases NOT using the pipeline is faster than using it, but using it is very much a “PowerShell” way of doing things. I like using the | Out-Null because it’s very easy to read, even for a beginner. Very few of my scripts require the kind of speed boost that using the other methods provide.
Ah Ok that makes sense, I do agree with the readability with Out-Null.