How do you run a SQL Server query from PowerShell?

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).

You can use the Invoke-Sqlcmd cmdlet

Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance "MyComputer\MyInstance"

http://technet.microsoft.com/en-us/library/cc281720.aspx

This function will return the results of a query as an array of powershell objects so you can use them in filters and access columns easily:

function sql($sqlText, $database = "master", $server = "."){    $connection = new-object System.Data.SqlClient.SQLConnection("Data Source=$server;Integrated Security=SSPI;Initial Catalog=$database");    $cmd = new-object System.Data.SqlClient.SqlCommand($sqlText, $connection);    $connection.Open();    $reader = $cmd.ExecuteReader()    $results = @()    while ($reader.Read())    {        $row = @{}        for ($i = 0; $i -lt $reader.FieldCount; $i++)        {            $row[$reader.GetName($i)] = $reader.GetValue($i)        }        $results += new-object psobject -property $row                }    $connection.Close();    $results}