So I have been “playing” a lot with Exchange, Powershell and SQL. Essentially on a daily basis, I get an export of all AD Users, mailboxes with stats and then pump it in to SQL, do some magic, and play with Excel Pivot tables and charts to give the powers that be an idea of the mailbox estate.
I wanted to share a simple function that I use run sql queries from powershell, to push or pull data.
Function Run-SqlQuery(
[string]$SQLserver,
[string]$SQLdb,
[string]$SQLcmd,
[string]$SQLUsername,
[string]$SQLUserPassword,
[switch]$verbose=$False){
$Error.Clear()
If($verbose){
Write-Host "SQL Server:..." $SQLserver
Write-Host "SQL Database:." $SQLdb
Write-Host "SQL User:....." $SQLUserName
Write-Host "SQL Pswd:....." $SQLUserPassword
Write-Host "Verbose:......" $Verbose
Write-Host "SQLcmd:......."
Write-Host $SQLcmd -foregroundcolor Yellow
}
$Connection = New-Object System.Data.SQLClient.SQLConnection
IF([string]::IsNullOrEmpty($SQLUserName)){
#Use Trusted Connection
Write-Host "Using a trusted connection" -foregroundcolor Green
$Connection.ConnectionString ="server=$SqlServer;database=$SqlDb;trusted_connection=true;"
}ELSE{
#Use UserName and Password
Write-Host "Using a specified username & password for connection" -foregroundcolor Green
$Connection.ConnectionString ="server=$SqlServer;database=$SqlDb;User ID=$SQLUserName;Password=$SQLUserPassword;"
}
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.CommandType = [System.Data.CommandType]"Text"
$Command.Connection = $Connection
If($Error.Count -gt 0){Exit}
$Command.CommandText = $SQLCmd
$Command.CommandTimeout = 0
$tmpDT = New-Object "System.Data.DataTable"
$Connection.Open()
$Reader = $Command.ExecuteReader()
$tmpDT.Load($Reader)
$Connection.Close()
$i=0;$tmpDT | ForEach{$i++}
Write-Host $i "Records returned"
Return $tmpDT
}
Enjoy, have some working examples coming soon too ;-) but in essence run it like:
$sqlquery = "Select * from sometable" $sqlserver = "mysqlserver" $sqldb = "mydb" run-sqlquery -SQLServer $sqlserver -SQLdb $sqldb -SQLcmd $sqlquery
This assumes the account you are using has trusted credentials, otherwise use the -SQLUserName and -SQLUserPassword switches
flaphead.com 
You can get native support for -Verbose by specifying cmdletbinding at the beginning of a function. And by wrapping your parameters in a param block, you get tab completion for them. This should work:
Function Run-SqlQuery{
[CmdletBinding(SupportsShouldProcess = $True)]
param {
[string] $SQLserver,
[string] $SQLdb,
[string] $SQLcmd,
[string] $SQLUsername,
[string] $SQLUserPassword
)
$Error.Clear()
Write-Verbose “SQL Server:…” $SQLserver
Write-Verbose “SQL Database:.” $SQLdb
Write-Verbose “SQL User:…..” $SQLUserName
Write-Verbose “SQL Pswd:…..” $SQLUserPassword
Write-Verbose “Verbose:……” $Verbose
Write-Verbose “SQLcmd:…….”
Write-Verbose $SQLcmd
$Connection = New-Object System.Data.SQLClient.SQLConnection
IF(!$SQLUserName){
#Use Trusted Connection
Write-Host “Using a trusted connection” -ForegroundColor Green
$Connection.ConnectionString =”server=$SqlServer;database=$SqlDb;trusted_connection=true;”
}ELSE{
#Use UserName and Password
Write-Host “Using a specified username & password for connection” -ForegroundColor Green
$Connection.ConnectionString =”server=$SqlServer;database=$SqlDb;User ID=$SQLUserName;Password=$SQLUserPassword;”
}
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.CommandType = [System.Data.CommandType]”Text”
$Command.Connection = $Connection
If($Error.Count -gt 0){Exit}
$Command.CommandText = $SQLCmd
$Command.CommandTimeout = 0
$tmpDT = New-Object “System.Data.DataTable”
$Connection.Open()
$Reader = $Command.ExecuteReader()
$tmpDT.Load($Reader)
$Connection.Close()
$i=0;$tmpDT | ForEach{$i++}
Write-Output $i “Records returned”
Return $tmpDT
}
Cool thanks for tip. Every day is a school day ;-)
tab completion does work, but I just don’t like the “look” of write-verbose which is why I write-host with an if statement
Yeah, I love playing with PowerShell.
The other issue is that Write-Host isn’t recommended because it doesn’t follow the pipeline. So Write-Output is preferred unless you’re using ForegroundColor, in which case you’re stuck with the Write-Host method.