PoshCode Archive  Artifact [1928930369]

Artifact 1928930369912434a7bd578a41a33384af8c960cf39f4ad0c04258e2dc4674db:

  • File Invoke-SqlCmd2.ps1 — part of check-in [0bbc44a8ae] at 2018-06-10 13:48:12 on branch trunk — Modeled after SQL Server 2008 Invoke-Sqlcmd, but fixes bug in QueryTimeout, and allows for paramaterized queries and more! (user: Joel Bennett size: 5775)

# 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