# encoding: ascii
# api: powershell
# title: Invoke-SqlCmd2 ()
# description: Modeled after SQL Server 2008 Invoke-Sqlcmd, but fixes bug in QueryTimeout, and allows for paramaterized queries and more!
# version: 1.5.1
# type: script
# author: Joel Bennett
# license: CC0
# function: Invoke-Sqlcmd2
# x-poshcode-id: 4961
# x-derived-from-id: 4967
# x-archived: 2016-09-06T14:50:43
# x-published: 2016-03-06T17:01:00
#
#
#######################
<#
.SYNOPSIS
Runs a T-SQL script.
.DESCRIPTION
Runs a T-SQL script. Invoke-Sqlcmd2 only returns message output, such as the output of PRINT statements when -verbose parameter is specified.
Paramaterized queries are supported.
.INPUTS
None
You cannot pipe objects to Invoke-Sqlcmd2
.OUTPUTS
System.Data.DataTable
.EXAMPLE
Invoke-Sqlcmd2 -ServerInstance "MyComputer\MyInstance" -Query "SELECT login_time AS 'StartTime' FROM sysprocesses WHERE spid = 1"
This example connects to a named instance of the Database Engine on a computer and runs a basic T-SQL query.
StartTime
-----------
2010-08-12 21:21:03.593
.EXAMPLE
Invoke-Sqlcmd2 -ServerInstance "MyComputer\MyInstance" -InputFile "C:\MyFolder\tsqlscript.sql" | Out-File -filePath "C:\MyFolder\tsqlscript.rpt"
This example reads a file containing T-SQL statements, runs the file, and writes the output to another file.
.EXAMPLE
Invoke-Sqlcmd2 -ServerInstance "MyComputer\MyInstance" -Query "PRINT 'hello world'" -Verbose
This example uses the PowerShell -Verbose parameter to return the message output of the PRINT command.
VERBOSE: hello world
.NOTES
Version History
v1.0 - Chad Miller - Initial release
v1.1 - Chad Miller - Fixed Issue with connection closing
v1.2 - Chad Miller - Added inputfile, SQL auth support, connectiontimeout and output message handling. Updated help documentation
v1.3 - Chad Miller - Added As parameter to control DataSet, DataTable or array of DataRow Output type
v1.4 - Justin Dearing <zippy1981 _at_ gmail.com> - Added the ability to pass parameters to the query.
v1.4.1 - Paul Bryson <atamido _at_ gmail.com> - Added fix to check for null values in parameterized queries and replace with [DBNull]
v1.5 - Joel Bennett - add SingleValue output option
v1.5.1 - RamblingCookieMonster - Added ParameterSets, set Query and InputFile to mandatory
#>
function Invoke-Sqlcmd2
{
[CmdletBinding(
DefaultParameterSetName='Query'
)]
param(
[Parameter( Position=0, Mandatory=$true)]
[string]$ServerInstance,
[Parameter( Position=1, Mandatory=$false)]
[string]$Database,
[Parameter( Position=2,
Mandatory=$true,
ParameterSetName="Query")]
[string]$Query,
[Parameter( Position=2,
Mandatory=$true,
ParameterSetName="File")]
[ValidateScript({test-path $_})]
[string]$InputFile,
[Parameter(Position=3, Mandatory=$false)]
[string]$Username,
[Parameter(Position=4, Mandatory=$false)]
[string]$Password,
[Parameter(Position=5, Mandatory=$false)]
[Int32]$QueryTimeout=600,
[Parameter(Position=6, Mandatory=$false)]
[Int32]$ConnectionTimeout=15,
[Parameter(Position=7, Mandatory=$false)]
[ValidateSet("DataSet", "DataTable", "DataRow","SingleValue")]
[string]$As="DataRow",
[Parameter(Position=8, Mandatory=$false)]
[System.Collections.IDictionary]$SqlParameters
)
if ($InputFile)
{
$filePath = $(Resolve-Path $InputFile).path
$Query = [System.IO.File]::ReadAllText("$filePath")
}
$conn = New-Object System.Data.SqlClient.SQLConnection
if ($Username)
{ $ConnectionString = "Server={0};Database={1};User ID={2};Password={3};Trusted_Connection=False;Connect Timeout={4}" -f $ServerInstance,$Database,$Username,$Password,$ConnectionTimeout }
else
{ $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout }
$conn.ConnectionString = $ConnectionString
#Following EventHandler is used for PRINT and RAISERROR T-SQL statements. Executed when -Verbose parameter specified by caller
if ($PSBoundParameters.Verbose)
{
$conn.FireInfoMessageEventOnUserErrors=$true
$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] { Write-Verbose "$($_)" }
$conn.add_InfoMessage($handler)
}
$conn.Open()
$cmd = New-Object system.Data.SqlClient.SqlCommand($Query,$conn)
$cmd.CommandTimeout=$QueryTimeout
if ($SqlParameters -ne $null)
{
$SqlParameters.GetEnumerator() |
ForEach-Object {
If ($_.Value -ne $null)
{ $cmd.Parameters.AddWithValue($_.Key, $_.Value) }
Else
{ $cmd.Parameters.AddWithValue($_.Key, [DBNull]::Value) }
} > $null
}
$ds = New-Object system.Data.DataSet
$da = New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$conn.Close()
switch ($As)
{
'DataSet' { Write-Output ($ds) }
'DataTable' { Write-Output ($ds.Tables) }
'DataRow' { Write-Output ($ds.Tables[0]) }
'SingleValue' { Write-Output ($ds.Tables[0] | Select-Object -Expand $ds.Tables[0].Columns[0].ColumnName ) }
}
} #Invoke-Sqlcmd2