As with all programming tasks there are a number of ways to do something, this is just one that I found recently, and that I found to be particularly useful. And by useful I mean it works, and it’s easy.
$myData = invoke-sqlcmd -InputFile $MyScript -serverinstance $SQLInstance
invoke-sqlcmd is a super easy, super useful command that you can use to run a sql query, or as in the example above, run a .sql script file. In this particular case I only need three variables.
- $MyScript – This is the name/path of the script file you want to run. (I said this was easy right?)
- $SQLInstance – The instance you want to connect to.
- $myData – This is the first result set from the script. Interestingly enough, if any subsequent result sets match the structure of the first, they are appended to the data set. In my opinion this is particularly powerful with a script file. Imagine creating a script that collects data on six different types of clients. They are all clients, and your result sets for each client are the same so all of that information ends up in $myData. In my particular case I used a script to pull data from the SQL log and pull back out the most recent logins/failed logins.
As with many of these types of commands there are also other handy parameters such as -Database, -UserName, and -Password just to name a few.
Last but not least, because I was able to pass in the instance name as a parameter I was able to throw this into a loop and hit several hundred instances to centralize some reporting.