Execution of Powershell SQL queries

In one of the project I was working for, I was asked to create general function which can help with executing Powershell SQL queries.
I prepared Powershell function which as an input need SQL server, database and query which should be executed. If database is located on specific instance remember to provide it as a part of SQL server parameter in format SQLServer\Instance
As a result table with filtered rows will be returned.

function Connect-SQL {
            [Parameter(Position=0, Mandatory=$true, HelpMessage="SQL query", ValueFromPipeline = $true)] $query,
            [Parameter(Position=1, Mandatory=$true, HelpMessage="SQL server instance name", ValueFromPipeline = $true)] $SqlSrv,
            [Parameter(Position=2, Mandatory=$true, HelpMessage="SQL database name", ValueFromPipeline = $true)] $SqlDb
        $ee = @()
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = "Server = $SqlSrv; Database =$SqlDb; Integrated Security = $True"        
        $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {
            param($sender, $event)
        $SqlConnection.FireInfoMessageEventOnUserErrors = $true;


            $SqlCmd = New-Object System.Data.SqlClient.SqlCommand 
            $SqlQuery1 = $query
            $SqlCmd.CommandText = $SqlQuery1 
            $SqlCmd.Connection = $SqlConnection 
            $SqlDa = New-Object System.Data.SqlClient.SqlDataAdapter
            $SqlDa.SelectCommand = $SqlCmd  
            $Ds = New-Object System.Data.DataSet
            $SQLResultCount = $SqlCmd.ExecuteNonQuery()            
            if($SQLResultCount -eq -1){
                [void] $SqlDa.Fill($Ds)
                $QueryResults = @()
                $Ds.Tables[0] | %{$QueryResults += $_}
                $SQLResultCount = $Ds.Tables[0].Rows.Count
            return $QueryResults
           return 'Error:' + $_.Exception.Message


Script of course needs remote access to SQL server, so before run ensure that port to SQL server is open 🙂
I hope that it will be useful for some of you.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.