Query SQL Server using Powershell

Just provide the required parameters to query your SQL Server.

$dbConnection: FQDN[,Port]

$dbName: database name

Param([string]$dbConnection, [string]$dbName)

function QuerySqlDatabase
    # Setup the SQL connection
    $connection = new-object system.data.sqlclient.sqlconnection("Data Source=$dbConnection;Initial Catalog=$dbName;Integrated Security=SSPI;");

    # Define TSQL
    [string]$query =
                your SQL statement here
    # Create the dataset and catch errors
    Try {
        $execute = new-object system.data.sqlclient.sqldataadapter ($query, $connection)
        $dataset = new-object system.data.dataset
        $execute.Fill($dataset) | Out-Null #speeds processing
    Catch {
        write-host "Exception while attempting to connect to SQL Server"
        $connectionDetails = $error[0]
        Write-Host "Exception details: $connectionDetails"
        Write-Host "Exiting"
    # Create an empty collection
    [System.Collections.ArrayList]$Rows = @()
    # Add each row in dataset to the collection
    foreach ($row in $dataset.Tables[0]) {
        $Rows.Add($row) | Out-Null #speeds processing
    # Pass collection to main script.
    return $Rows

QuerySqlDatabase $dbConnection $dbName

Comments welcome (links require moderation)