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

 

One thought on “Using #Powershell to run a SQL Command

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.