February 12, 2014 – flaphead.com

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

Day: February 12, 2014

#Powershell and SQL