Home > Powershell, Scripting, Tech Tips, Toolbox > Powershell – function to Query a SQL database

Powershell – function to Query a SQL database

# Today’s quick and easy function is a simple one that I regularly use to query a SQL database.

It will return an object containing the result of your query – so makes SQL access very simply from Powershell

Function QuickQuery-SQL {
## Performs a T-SQL query against an SQL 2000/2005/2008
## with the result returned as as a PowerShell object.
## QuickQuery-SQL "server" "database" "t-sql query"
##Usage:
## Find NodeID from FindIt DB
## $output = QuickQuery-SQL "MySqlServername" "MyDatabase" "SELECT * FROM MyTable WHERE name = 'MySearchValue'"
Param ($server = "MyServer\MyInstance",
$database = "master",
$query = "SELECT * FROM sysdatabases",
$connectionName = "PS QuickQuery SQL",
$commandTimeout = 15)
$conn = <strong>new-object</strong> ('System.Data.SqlClient.SqlConnection')
$connString = "Server=$server;Integrated Security=SSPI;Database=$database;Application Name=$connectionName"
$conn.ConnectionString = $connString
<strong>Write-Debug</strong> ("Function: Query-SQL: $server $database")
if (<strong>test-path</strong> variable:\conn) {
$conn.close()
} else {
$conn = <strong>new-object</strong> ('System.Data.SqlClient.SqlConnection')
}
$conn.Open()
$sqlCmd = <strong>New-Object</strong> System.Data.SqlClient.SqlCommand
$sqlCmd.CommandTimeout = $CommandTimeout
$sqlCmd.CommandText = $query
$sqlCmd.Connection = $conn
$data = $sqlCmd.ExecuteReader()
while ($data.read() -eq $true) {
$max = $data.FieldCount -1
$obj = <strong>New-Object</strong> Object
For ($i = 0; $i -le $max; $i++) {
$name = $data.GetName($i)
if ($name.length -eq 0) {
$name = "field$i"
}
$obj | <strong>Add-Member</strong> Noteproperty $name <em>-value</em> $data.GetValue($i) -Force
}
$obj
}
$conn.close()
$conn = $null
}
Advertisements
  1. No comments yet.
  1. No trackbacks yet.

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: