Invoke-Sqlcmd

The Invoke-Sqlcmd cmdlet is a SQL Server cmdlet that runs scripts that contain statements from the languages (Transact-SQL and XQuery) and commands that are supported by the sqlcmd utility.

Great! Let’s use it.

The term 'invoke-sqlcmd' is not recognized as the name of a cmdlet, function, script file, or operable program.

Damn! But why?
Because, Invoke-sqlcmd isn’t included with PowerShell, it’s part of the SQL Server 2008 PowerShell Host (sqlps). So, the first thing we’ll have to do is to install SQL Server Management Studio 2008 (Express version would be enough) or install the SQL Server 2008 Feature Pack.
I mean seriously man? Just to run a query!?

Allright, that’s where the following function comes in!

function Invoke-SQL {
param(
    [Parameter(Mandatory=$true)]
    [string]$Server,
    [Parameter(Mandatory=$true)]
    [string]$dbName,
    [Parameter(Mandatory=$true)]
    [string]$sqlQuery
)

$ConnectString="Data Source=${Server}; Integrated Security=SSPI; Initial Catalog=${dbName}"

$Conn=New-Object System.Data.SqlClient.SQLConnection($ConnectString)
$Command = New-Object System.Data.SqlClient.SqlCommand($sqlQuery,$Conn)
$Conn.Open()

$Adapter = New-Object System.Data.SqlClient.SqlDataAdapter $Command
$DataSet = New-Object System.Data.DataSet
$Adapter.Fill($DataSet) | Out-Null

$Conn.Close()
$DataSet.Tables
}

So now, we can run a query, for example

Invoke-SQL -Server "MSSQLSRV" -dbName "master" -sqlQuery "SELECT GETDATE() AS TimeOfQuery;"