PowerShell run a TSQL query on a SQL Server instance and return the query results

Invoke-SqlQuery.ps1

<#
 .Synopsis
 Run the TSQL query on a SQL Server instance and return the query results. Use SELECT rather than PRINT to get output.

 .Description
 Run the TSQL query on a SQL Server instance and return the query results. Use SELECT rather than PRINT to get output.
#>
function Invoke-SqlQuery([string]$ComputerName = $Env:COMPUTERNAME,
    [string]$InstanceName = "MSSQLSERVER",
    [string]$Database = [System.String]::Empty,
    [string]$Query)
{
    [int]$ExitCode = 0
    [string]$ServerInstance = $ComputerName
    if ($InstanceName -ne "MSSQLSERVER")
    {
        $ServerInstance += "\$InstanceName"
    }
    
    # Import SQL Server module (2012+) if not imported or add snapin (2008/2008R2):
    $SQLPS = Get-Module -Name "SQLPS"
    if ($SQLPS -eq $null)
    {
        $SQLPS = Get-Module -ListAvailable -Name "SQLPS"
        if ($SQLPS -ne $null)
        {
            Write-Log "Importing module for SQL..."
            Import-Module -Name "SQLPS" -DisableNameChecking
        }
        else
        {
            $SQLSnapin = Get-PSSnapin -Registered -Name "SqlServerCmdletSnapin*" -ErrorAction SilentlyContinue
            if ($SQLSnapin -ne $null)
            {
                Write-Log "Adding snapin for SQL..."
                Add-PSSnapin $SQLSnapin -ErrorAction SilentlyContinue
            }
            else
            {
                throw "Error - Neither SQLPS module nor SqlServerCmdletSnapin found."
            }
        }
    }
    else
    {
        Write-Log "Module for SQL already imported."
    }

    # Invoke SQL.
    # Note: Invoke-SqlCmd does not return output from PRINT statements.
    # In PowerShell 2.0 there is no way to capture PRINT output as it is sent to the verbose stream.
    # In 3.0 the Verbose stream can be redirected with 4>&1, but at this point PowerShell 2.0 must
    # be supported and even having "&" in a script line that is not run will produce errors.
    $SqlOutput = Invoke-SqlCmd -ServerInstance $ServerInstance -OutputSqlErrors $true -Query $Query -Verbose

    # Output from returned rows:
    if ($SqlOutput -ne $null)
    {
        Write-Log "SQL output:"
        foreach ($Row in $SqlOutput)
        {
            [string]$RowString = [System.String]::Empty
            $Row.ItemArray | % { $RowString += "$_, " }
            Write-Log $RowString.TrimEnd(@(","," "))
            if ($Row.HasErrors)
            {
                $ExitCode++
            }
        }
    }
    
    return $ExitCode
}