Monday, 28 January 2013

Running SQL queries in PowerShell

Sometimes I come across such a requirement. It is quite useful for many many scripts.
Whenever I search for this information I come up with long code sinplets of various optimisation level.
On my last search for this I found an actual PowerShell function for executing SQL queries. I rejoiced but not for long. While this worked very well, it required the local installation of the SQL management tools. Quite a tall order for something that should work practically anywhere.
So I decided to write my own (simplified) SQL query execution function.

Here's the result.
Function Execute-SqlQuery {
    Param (
        #SQL server name.
        #SQL query to execute.
        #Database name on the SQL server.
    Process {
        #Only basic connection string support implemented. Always use Integrated Security.
        $ConnStr = "Server=$Server; Database=$Database; Integrated Security=SSPI;"

        #Create SQL connection object and pass connection string.
        $SqlConn = New-Object System.Data.SqlClient.SqlConnection
        $SqlConn.ConnectionString = $ConnStr

        #Create SQL command object and apply the connection to it.
        $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
        $SqlCmd.CommandText = $Query
        $SqlCmd.Connection = $SqlConn

        #Crate SQL adapter object to return query data to the end user.
        $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
        $SqlAdapter.SelectCommand = $SqlCmd

        #Fill the SQL query results into a temporary DataSet object.
        $Results = New-Object System.Data.DataSet
        $SqlAdapter.Fill($Results) | Out-Null

        #Clean up

        Return $Results.Tables[0]

I hope you all find this useful. It's a big time saver for sure.

No comments: