Query SQL Server from PowerShell without the SQL module or snapin

There are several different ways to query a SQL Server from PowerShell but most often you'll find that they're dependent on the SQL PowerShell module or snapin.

To eliminate that dependency, you can query a SQL Server from PowerShell using the .NET framework. There are several different options to accomplish this including using a DataReader or a DataSet and there are plenty of tutorials on those topics already on the Internet so there's no reason to duplicate that information here. Most of the tutorials are developer focused, but you should be able to translate them to PowerShell easy enough.

The PowerShell function shown in the following example uses a DataReader along with a DataTable to retrieve the information from SQL Server and display it in PowerShell. One Transact-SQL (TSQL) select statement can be piped into this function or provided via parameter input. The function also includes comment based help and error handling. Either a trusted database connection or SQL authentication can be used.

  1#Requires -Version 3.0
  2function Invoke-MrSqlDataReader {
  3
  4<#
  5.SYNOPSIS
  6    Runs a select statement query against a SQL Server database.
  7
  8.DESCRIPTION
  9    Invoke-MrSqlDataReader is a PowerShell function that is designed to query
 10    a SQL Server database using a select statement without the need for the SQL
 11    PowerShell module or snap-in being installed.
 12
 13.PARAMETER ServerInstance
 14    The name of an instance of the SQL Server database engine. For default instances,
 15    only specify the server name: 'ServerName'. For named instances, use the format
 16    'ServerName\InstanceName'.
 17
 18.PARAMETER Database
 19    The name of the database to query on the specified SQL Server instance.
 20
 21.PARAMETER Query
 22    Specifies one Transact-SQL select statement query to be run.
 23
 24.PARAMETER Credential
 25    SQL Authentication userid and password in the form of a credential object.
 26
 27.EXAMPLE
 28     Invoke-MrSqlDataReader -ServerInstance Server01 -Database Master -Query '
 29     select name, database_id, compatibility_level, recovery_model_desc from sys.databases'
 30
 31.EXAMPLE
 32     'select name, database_id, compatibility_level, recovery_model_desc from sys.databases' |
 33     Invoke-MrSqlDataReader -ServerInstance Server01 -Database Master
 34
 35.EXAMPLE
 36     'select name, database_id, compatibility_level, recovery_model_desc from sys.databases' |
 37     Invoke-MrSqlDataReader -ServerInstance Server01 -Database Master -Credential (Get-Credential)
 38
 39.INPUTS
 40    String
 41
 42.OUTPUTS
 43    DataRow
 44
 45.NOTES
 46    Author:  Mike F Robbins
 47    Website: http://mikefrobbins.com
 48    Twitter: @mikefrobbins
 49#>
 50
 51    [CmdletBinding()]
 52    param (
 53        [Parameter(Mandatory)]
 54        [string]$ServerInstance,
 55
 56        [Parameter(Mandatory)]
 57        [string]$Database,
 58
 59        [Parameter(Mandatory,
 60                   ValueFromPipeline)]
 61        [string]$Query,
 62
 63        [System.Management.Automation.Credential()]$Credential = [System.Management.Automation.PSCredential]::Empty
 64    )
 65
 66    BEGIN {
 67        $connection = New-Object -TypeName System.Data.SqlClient.SqlConnection
 68
 69        if (-not($PSBoundParameters.Credential)) {
 70            $connectionString = "Server=$ServerInstance;Database=$Database;Integrated Security=True;"
 71        }
 72        else {
 73            $connectionString = "Server=$ServerInstance;Database=$Database;Integrated Security=False;"
 74            $userid= $Credential.UserName -replace '^.*\\|@.*$'
 75            ($password = $credential.Password).MakeReadOnly()
 76            $sqlCred = New-Object -TypeName System.Data.SqlClient.SqlCredential($userid, $password)
 77            $connection.Credential = $sqlCred
 78        }
 79
 80        $connection.ConnectionString = $connectionString
 81        $ErrorActionPreference = 'Stop'
 82
 83        try {
 84            $connection.Open()
 85            Write-Verbose -Message "Connection to the $($connection.Database) database on $($connection.DataSource) has been successfully opened."
 86        }
 87        catch {
 88            Write-Error -Message "An error has occurred. Error details: $($_.Exception.Message)"
 89        }
 90
 91        $ErrorActionPreference = 'Continue'
 92        $command = $connection.CreateCommand()
 93    }
 94
 95    PROCESS {
 96        $command.CommandText = $Query
 97        $ErrorActionPreference = 'Stop'
 98
 99        try {
100            $result = $command.ExecuteReader()
101        }
102        catch {
103            Write-Error -Message "An error has occured. Error Details: $($_.Exception.Message)"
104        }
105
106        $ErrorActionPreference = 'Continue'
107
108        if ($result) {
109            $dataTable = New-Object -TypeName System.Data.DataTable
110            $dataTable.Load($result)
111            $dataTable
112        }
113    }
114
115    END {
116        $connection.Close()
117    }
118
119}

powershell-sql-datareader.jpg

For more information and options on querying SQL Server using the .NET framework, see:

The Invoke-MrSqlDataReader PowerShell function shown in this blog article can be downloaded as part of my MrSQL PowerShell module on GitHub.

µ