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}
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.
µ