Home > Powershell, 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 =new-object ('System.Data.SqlClient.SqlConnection')
$connString = "Server=$server;Integrated Security=SSPI;Database=$database;Application Name=$connectionName"
$conn.ConnectionString = $connString
Write-Debug ("Function: Query-SQL: $server $database")
if (test-path variable:\conn) {
$conn.close()
} else {
$conn =new-object ('System.Data.SqlClient.SqlConnection')
}
$conn.Open()
$sqlCmd =New-Object 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 =New-Object Object
For ($i = 0; $i -le $max; $i++) {
$name = $data.GetName($i)
if ($name.length -eq 0) {
$name = "field$i"
}
$obj |Add-Member Noteproperty $name -value $data.GetValue($i) -Force
}
$obj
}
$conn.close()
$conn = $null
}
  1. luke
    March 15th, 2011 at 19:28 | #1

    Any chance you could fix up the formatting of the Powershell code?

    • alanvanwyk
      March 15th, 2011 at 19:43 | #2

      Updated from my mobile – so untested . .but should be OK

  1. No trackbacks yet.
You must be logged in to post a comment.