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

If you want to run a SQL Server query from PowerShell with just stock .net and PowerShell (no additional SQL tools installed), use the function as follows:

QUERY:

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

}