#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

Comments

  1. 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.

Trackbacks

  1. […] following on from my last post (http://blog.flaphead.com/2014/02/12/powershell-and-sql/) I wanted to add some SQL queries to […]

  2. […] pure PowerShell. This is a script where I’m using a modified version Paul Flaherty’s SQL query function. This makes life a LOT easier. I’ve used a lot before as it saves […]

Leave a Reply

%d bloggers like this: