How do you run a SQL Server query from PowerShell?

Question

Is there a way to execute an arbitrary query on a SQL Server using Powershell on my local machine?

Answer 1

For others who need to do this with just stock .NET and PowerShell (no additional SQL tools installed) here is the function that I use:

function Invoke-SQL {
    param(
        [string] $dataSource = ".\SQLEXPRESS",
        [string] $database = "MasterData",
        [string] $sqlCommand = $(throw "Please specify a query.")
      )

    $connectionString = "Data Source=$dataSource; " +
            "Integrated Security=SSPI; " +
            "Initial Catalog=$database"

    $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
    $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
    $connection.Open()
    
    $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
    $dataset = New-Object System.Data.DataSet
    $adapter.Fill($dataSet) | Out-Null
    
    $connection.Close()
    $dataSet.Tables

}

I have been using this so long I don't know who wrote which parts. This was distilled from others' examples, but simplified to be clear and just what is needed without extra dependencies or features.

I use and share this often enough that I have turned this into a script module on GitHub so that you can now go to your modules directory and execute git clone https://github.com/ChrisMagnuson/InvokeSQL and from that point forward invoke-sql will automatically be loaded when you go to use it (assuming your using PowerShell v3 or later).