[powershell] How do you run a SQL Server query from PowerShell?

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

This question is related to powershell sql

The answer is


To avoid SQL Injection with varchar parameters you could use

function sqlExecuteRead($connectionString, $sqlCommand, $pars) {

    $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
    $connection.Open()
    $command = new-object system.data.sqlclient.sqlcommand($sqlCommand, $connection)

    if ($pars -and $pars.Keys) {
        foreach($key in $pars.keys) {
            # avoid injection in varchar parameters
            $par = $command.Parameters.Add("@$key", [system.data.SqlDbType]::VarChar, 512);
            $par.Value = $pars[$key];
        }
    }

    $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
    $dataset = New-Object System.Data.DataSet
    $adapter.Fill($dataset) | Out-Null
    $connection.Close()
    return $dataset.tables[0].rows

}

$connectionString = "connectionstringHere"
$sql = "select top 10 Message, TimeStamp, Level from dbo.log " +
    "where Message = @MSG and Level like @LEVEL"
$pars = @{
    MSG = 'this is a test from powershell'
    LEVEL = 'aaa%'
};
sqlExecuteRead $connectionString $sql $pars

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


Here's an example I found on this blog.

$cn2 = new-object system.data.SqlClient.SQLConnection("Data Source=machine1;Integrated Security=SSPI;Initial Catalog=master");
$cmd = new-object system.data.sqlclient.sqlcommand("dbcc freeproccache", $cn2);
$cn2.Open();
if ($cmd.ExecuteNonQuery() -ne -1)
{
    echo "Failed";
}
$cn2.Close();

Presumably you could substitute a different TSQL statement where it says dbcc freeproccache.


Invoke-Sqlcmd -Query "sp_who" -ServerInstance . -QueryTimeout 3

There isn't a built-in "PowerShell" way of running a SQL query. If you have the SQL Server tools installed, you'll get an Invoke-SqlCmd cmdlet.

Because PowerShell is built on .NET, you can use the ADO.NET API to run your queries.


If you want to do it on your local machine instead of in the context of SQL server then I would use the following. It is what we use at my company.

$ServerName = "_ServerName_"
$DatabaseName = "_DatabaseName_"
$Query = "SELECT * FROM Table WHERE Column = ''"

#Timeout parameters
$QueryTimeout = 120
$ConnectionTimeout = 30

#Action of connecting to the Database and executing the query and returning results if there were any.
$conn=New-Object System.Data.SqlClient.SQLConnection
$ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerName,$DatabaseName,$ConnectionTimeout
$conn.ConnectionString=$ConnectionString
$conn.Open()
$cmd=New-Object system.Data.SqlClient.SqlCommand($Query,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$conn.Close()
$ds.Tables

Just fill in the $ServerName, $DatabaseName and the $Query variables and you should be good to go.

I am not sure how we originally found this out, but there is something very similar here.


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
}

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