Using #Powershell to run a SQL Command

So I have a hell of a lot of stuff where I am pushing or pulling data from SQL and wanted to share the function I use:

Function RunSqlQuery([string]$SqlServer, [string]$SqlDb, [string]$SqlCmd){
$Error.Clear()
Write-Host “SQL Server…: ” $SqlServer
Write-Host “Sql Database.: ” $SqlDb

$Connection = New-Object System.Data.SQLClient.SQLConnection

$Connection.ConnectionString =”server=$SqlServer;database=$SqlDb;trusted_connection=true;”

$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

$tmpDT = New-Object “System.Data.DataTable”
$Connection.Open()
$Reader = $Command.ExecuteReader()
$tmpDT.Load($Reader)
$Connection.Close()

$i=0;$tmpDT | ForEach{$i++}
Write-Host “Query Result size: ” $i
Write-Host “`n”

Return $tmpDT
}

The function assumes the account using the function has rights on the SQL database.

Typically I set the following:

$SqlServer = “SQLServer”
$SqlDB = “SQLDatabase”
$SqlQuery = “Select * from something”

RunSqlQuery $SqlServer $sqldb $SQLQuery

-or-

$SqlData = RunSqlQuery $SqlServer $sqldb $SQLQuery

 

Trackbacks

  1. […] following on from my last post (https://blog.flaphead.com/2013/04/23/using-powershell-to-run-a-sql-command/) I use this SQL query to dump BlackBerry User information in to a #Powershell array so I can export […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: