It’s actually pretty easy to throw a SQL query against Sql Server using PowerShell. This would be very familiar to any C# or FoxPro developer, so don’t let the mystery of Power Shell scare you away from exploring this handy tool.
Here’s a tiny code snippet to show you how simple it is:
Note that the call to the DoSql() function is a wrapper function that I wrote which creates a few PowerShell objects to do all the low level work so that you don’t have to repeat all that connection and plumbing stuff in your scripts. Once you have that in place, executing Sql queries is a piece of cake. After you get back the query results, PowerShell has all the standard language features you’d expect such as looping and counting, and even some cool sorting and filtering tricks that are pretty handy for working with the data rows.
Helper functions
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
function DoSql($sql) { # https://cmatskas.com/execute-sql-query-with-powershell/ $SqlConnection = GetSqlConnection $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = $sql $SqlCmd.Connection = $SqlConnection $Reader= $SqlCmd.ExecuteReader() $DataTable = New-Object System.Data.DataTable $DataTable.Load($Reader) $SqlConnection.Close() return $DataTable } |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
function GetSqlConnection { $sqlServerInstance = "192.168.0.99\SQLExpress" $database = "MyDB" $userName = "UserName" $password = "Password" $ConnectionString = "Server=$sqlServerInstance; database=$database; Integrated Security=False;" + "User ID=$userName; Password=$password;" try { $sqlConnection = New-Object System.Data.SqlClient.SqlConnection $ConnectionString $sqlConnection.Open() return $sqlConnection } catch { return $null } } |
From here, it becomes really easy to add system admin things shooting off an email, or calling a Stored Proc, or writing a Windows Event log entry, or call an external API, or blah-blah-blah.
Every modern version of Windows has PowerShell pre-installed, and it even has a simple IDE (Called PowerShell ISE ) to give you a coding environment with intellisense, code completion, and debugging tools.
Note: I found this article very helpful in learning how to do this: https://cmatskas.com/execute-sql-query-with-powershell/
Great post, Matt!
Glad I found this code snippet! Works great – thanks for posting.
I tried the example but it didnt work, I get an error “DoSql: The term “DoSql” is not recognized as a name of a cmdlet, function, script file, or operable program.
Both my utils.ps1 and my main.ps1 are in the same directory.
@BuckPowell –
Are you sure you have the include command to reference your utils.ps1 file? It is not enough that they are in the same folder. the pain script file has to reference this utils.ps1 to it will be loaded in memory at run time.
The format is obscure, because you simple put a period followed by the script you want to reference in. Like this:
. ‘utils.ps1’