#Powershell and SQL

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