A piece of my work around PowerShell and IIS (or usefull things I've found on the web). Mostly some reminders for myself!

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;"

Name*Email*WebsiteComment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top