Invoke-TSQLCmd: Execute queries against a SQL database and get back results

Topics: Archive - Toolkit Extensions
Developer
Jul 9, 2014 at 11:30 PM
I am using a custom logging function posted on another thread in this forum. Write-ErrorStack function is also posted to another thread in this forum.
Function Invoke-TSQLCmd
{
<#
.SYNOPSIS
    Runs a T-SQL script. Invoke-TSQLCmd can handle T-SQL script with multiple GO statements. Do not add GO for the very last GO block.
    
.DESCRIPTION
    Runs a T-SQL script. Invoke-TSQLCmd returns message output, such as the output of PRINT and RAISERROR.
    Paramaterized queries are supported.
    
.INPUTS
    None
    You cannot pipe objects to this function.
    
.OUTPUTS
   System.Data.DataTable
    
.EXAMPLE
    Invoke-TSQLCmd -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-TSQLCmd -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.
    
.NOTES
#>
    [CmdletBinding(DefaultParameterSetName='Query')]
    Param
    (
        [Parameter(Position=0, Mandatory=$true)]
        [ValidateNotNullorEmpty()]
        [string]$ServerInstance,
    
        [Parameter(Position=1, Mandatory=$false)]
        [ValidateNotNullorEmpty()]
        [string]$Database,
        
        # If you pass a query with multiple GO statements, do not add GO to the very last statement.
        [Parameter(Position=2, Mandatory=$true, ParameterSetName="Query")]
        [ValidateNotNullorEmpty()]
        [string]$Query,
        
        # If you pass a query with multiple GO statements, do not add GO to the very last statement.
        [Parameter(Position=2, Mandatory=$true, ParameterSetName="File")]
        [ValidateNotNullorEmpty()]
        [ValidateScript({Test-Path $_})]
        [string]$InputFile,

        [Parameter(Position=3, Mandatory=$false)]
        [ValidateNotNullorEmpty()]
        [string]$Username,
    
        [Parameter(Position=4, Mandatory=$false)]
        [ValidateNotNullorEmpty()]
        [string]$Password,
        
        # The time (in seconds) to wait for the command to execute
        [Parameter(Position=5, Mandatory=$false)]
        [ValidateNotNullorEmpty()]
        [Int32]$QueryTimeout=120,
        
        # The time (in seconds) to wait for a connection to open
        [Parameter(Position=6, Mandatory=$false)]
        [ValidateNotNullorEmpty()]
        [Int32]$ConnectionTimeout=120,
    
        [Parameter(Position=7, Mandatory=$false)]
        [ValidateSet("DataSet", "DataTables", "DataRows","SingleValue")]
        [string]$OutputFormat="DataRows",
    
        [Parameter(Position=8, Mandatory=$false)]
        [ValidateNotNullorEmpty()]
        [System.Collections.IDictionary]$SqlParameters
    )
    
    Begin
    {
        Function Get-SqlBatchesFromString($string)
        {
            # If you pass a query with multiple GO statements, do not add GO to the very last statement.
            $buffer = New-Object System.Text.StringBuilder
            
            $string -Split "`n" | ForEach {
                Switch -regex ($_)
                {
                    "^\s*GO[\s\d]*$" { $buffer.ToString(); $buffer.Length = 0 }
                    default          { $temp = $buffer.AppendLine($_) }
                }
            }
            $buffer.ToString()
        }
        
        ${CmdletName} = $PSCmdlet.MyInvocation.MyCommand.Name
        $PSParameters = New-Object -TypeName PSObject -Property $PSBoundParameters
        
        Write-Log -Message "Function Start" -Component ${CmdletName} -Severity 1
        If (-not [string]::IsNullOrEmpty($PSParameters))
        {
            Write-Log -Message "Function invoked with bound parameters [$PSParameters]" -Component ${CmdletName} -Severity 1
        }
        Else
        {
            Write-Log -Message "Function invoked without any bound parameters" -Component ${CmdletName} -Severity 1
        }
    }
    Process
    {
        Try
        {
            If ($InputFile)
            {
                $FilePath = $(Resolve-Path $InputFile).Path
                [string]$Query    =  [System.IO.File]::ReadAllText("$FilePath")
            }
            
            $SQLConnection = 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
            }
            
            # Set the connection string used to open a connection to the data source
            $SQLConnection.ConnectionString = $ConnectionString
            
            # Following EventHandler is used for logging PRINT and RAISERROR T-SQL statements.
            # When you set FireInfoMessageEventOnUserErrors to true, errors with a severity level of 16 or below that were previously
            #   treated as exceptions are now handled as InfoMessage events.
            # All events fire immediately and are handled by the event handler.
            # If FireInfoMessageEventOnUserErrors is set to false, then InfoMessage events are handled at the end of the procedure.
            $SQLConnection.FireInfoMessageEventOnUserErrors = $true
            
            # Define method that will handle InfoMessage events which are fired when SQL commands are processed
            # We will log all messages generated by processing of SQL commands
            $EventHandler = [System.Data.SqlClient.SqlInfoMessageEventHandler]{Write-Log -Message "SQL InfoMessage: $($_)" -Component ${CmdletName} -Severity 2}
            
            # Add_InfoMessage occurs when SQL Server returns a warning or informational message
            $SQLConnection.Add_InfoMessage($EventHandler)
            
            # Open a connection to the data source
            Write-Log -Message "Open connection to data source" -Component ${CmdletName} -Severity 1
            $SQLConnection.Open()
            
            # Define object for holding the results of the T-SQL statement or stored procedure
            $DataSet = New-Object System.Data.DataSet
            
            Get-SqlBatchesFromString $Query | % {
                # If you pass a query with multiple GO statements, do not add GO to the very last statement.
                
                If ([string]::IsNullOrEmpty($_))
                {
                    Continue
                }
                
                # Configure the T-SQL statement or stored procedure to be executed against the data source
                $SQLCmd = New-Object System.Data.SqlClient.SqlCommand($_,$SQLConnection)
                
                # Configure the wait time (in seconds) before terminating the attempt to execute the T-SQL statement or stored procedure
                $SQLCmd.CommandTimeout = $QueryTimeout
                
                # Add any specified parameters to the T-SQL command being executed
                If ($SqlParameters -ne $null)
                {
                    $SqlParameters.GetEnumerator() |
                        ForEach-Object
                        {
                            If ($_.Value -ne $null)
                            {
                                $SQLCmd.Parameters.AddWithValue($_.Key, $_.Value) | Out-Null
                            }
                            Else
                            {
                                $SQLCmd.Parameters.AddWithValue($_.Key, [DBNull]::Value) | Out-Null
                            }
                        }
                }
                
                # Define object to represent a set of data commands and a database connection
                $DataSource = New-Object System.Data.SqlClient.SqlDataAdapter($SQLCmd)
                
                # Execute the T-SQL statement or stored procedure against the data source and store the results
                # The Fill method retrieves the data from the data source using a SELECT statement.
                # If a command does not return any rows, no tables are added to the DataSet, and no exception is raised.
                # http://msdn.microsoft.com/en-us/library/zxkb3c3d.aspx
                Write-Log -Message "Execute T-SQL batch `n$_" -Component ${CmdletName} -Severity 1
                [void]$DataSource.Fill($DataSet)
            }
            
            # Output results
            Switch ($OutputFormat)
            {
                'DataSet'     {
                                Write-Output ($DataSet)
                              }
                'DataTables'  {
                                Write-Output ($DataSet.Tables | Select-Object -Expand Rows)
                              }
                'DataRows'    {
                                Write-Output ($DataSet.Tables[0])
                              }
                'SingleValue' {
                                Write-Output ($DataSet.Tables[0] | Select-Object -Expand $DataSet.Tables[0].Columns[0].ColumnName)
                              }
            }
        }
        Catch
        {
            Write-Log -Message "Failed to execute T-SQL statement `n$(Write-ErrorStack)" -Component ${CmdletName} -Severity 3
            Exit
        }
        Finally
        {
            # Close the connection to the data source if it is open and release all resources
            If ($SQLConnection -and ($SQLConnection.State -ne [System.Data.ConnectionState]::Closed))
            {
                Write-Log -Message "Closing connection to data source and releasing all resources" -Component ${CmdletName} -Severity 1
                $SQLConnection.Close()
                $SQLConnection.Dispose()
            }
        }
    }
    End
    {
        Write-Log -Message "Function End" -Component ${CmdletName} -Severity 1
    }
}