Skip to content

Instantly share code, notes, and snippets.

@ismits
Created September 24, 2019 16:53
Show Gist options
  • Save ismits/94913d0a9b27e5a06f833598996784a8 to your computer and use it in GitHub Desktop.
Save ismits/94913d0a9b27e5a06f833598996784a8 to your computer and use it in GitHub Desktop.
PowerShell run a TSQL query on a SQL Server instance and return the query results
<#
.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
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment