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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 | #Requires -Version 3.0 function Invoke-MrSqlDataReader { <# .SYNOPSIS Runs a select statement query against a SQL Server database. .DESCRIPTION Invoke-MrSqlDataReader is a PowerShell function that is designed to query a SQL Server database using a select statement without the need for the SQL PowerShell module or snap-in being installed. .PARAMETER ServerInstance The name of an instance of the SQL Server database engine. For default instances, only specify the server name: 'ServerName'. For named instances, use the format 'ServerName\InstanceName'. .PARAMETER Database The name of the database to query on the specified SQL Server instance. .PARAMETER Query Specifies one Transact-SQL select statement query to be run. .PARAMETER Credential SQL Authentication userid and password in the form of a credential object. .EXAMPLE Invoke-MrSqlDataReader -ServerInstance Server01 -Database Master -Query ' select name, database_id, compatibility_level, recovery_model_desc from sys.databases' .EXAMPLE 'select name, database_id, compatibility_level, recovery_model_desc from sys.databases' | Invoke-MrSqlDataReader -ServerInstance Server01 -Database Master .EXAMPLE 'select name, database_id, compatibility_level, recovery_model_desc from sys.databases' | Invoke-MrSqlDataReader -ServerInstance Server01 -Database Master -Credential (Get-Credential) .INPUTS String .OUTPUTS DataRow .NOTES Author: Mike F Robbins Website: http://mikefrobbins.com Twitter: @mikefrobbins #> [CmdletBinding()] param ( [Parameter(Mandatory)] [string]$ServerInstance, [Parameter(Mandatory)] [string]$Database, [Parameter(Mandatory, ValueFromPipeline)] [string]$Query, [System.Management.Automation.Credential()]$Credential = [System.Management.Automation.PSCredential]::Empty ) BEGIN { $connection = New-Object -TypeName System.Data.SqlClient.SqlConnection if (-not($PSBoundParameters.Credential)) { $connectionString = "Server=$ServerInstance;Database=$Database;Integrated Security=True;" } else { $connectionString = "Server=$ServerInstance;Database=$Database;Integrated Security=False;" $userid= $Credential.UserName -replace '^.*\\|@.*$' ($password = $credential.Password).MakeReadOnly() $sqlCred = New-Object -TypeName System.Data.SqlClient.SqlCredential($userid, $password) $connection.Credential = $sqlCred } $connection.ConnectionString = $connectionString $ErrorActionPreference = 'Stop' try { $connection.Open() Write-Verbose -Message "Connection to the $($connection.Database) database on $($connection.DataSource) has been successfully opened." } catch { Write-Error -Message "An error has occurred. Error details: $($_.Exception.Message)" } $ErrorActionPreference = 'Continue' $command = $connection.CreateCommand() } PROCESS { $command.CommandText = $Query $ErrorActionPreference = 'Stop' try { $result = $command.ExecuteReader() } catch { Write-Error -Message "An error has occured. Error Details: $($_.Exception.Message)" } $ErrorActionPreference = 'Continue' if ($result) { $dataTable = New-Object -TypeName System.Data.DataTable $dataTable.Load($result) $dataTable } } END { $connection.Close() } } |
For more information and options on querying SQL Server using the .NET framework, see the DataAdapters and DataReaders, DataTable Class, and DataSet Class topics on MSDN.
The Invoke-MrSqlDataReader PowerShell function shown in this blog article can be downloaded as part of my MrSQL PowerShell module on GitHub.
µ
Cool article Mike. I’ll have to take your script for spin.
I wrote something similar not long ago. https://github.com/michaellwest/PowerShell-Modules/blob/master/CorpApps/Invoke-SqlCommand.ps1
Nice function. Works great! Thanks.
Hello.
Nice Post, Very Simple.
I changed your code in order to save the output to XML. This feature can answer to a lot for problems related to the size of the output.
It’s just a little change, i will keep you as the only author.
Many thanks.
Pedro, O Moleiro
function Invoke-MrSqlXmlDataReader {
[CmdletBinding()]
param (
[Parameter(Mandatory)]
[string]$ServerInstance,
[Parameter(Mandatory)]
[string]$Database,
[Parameter(Mandatory,
ValueFromPipeline)]
[string]$Query,
[System.Management.Automation.Credential()]$Credential = [System.Management.Automation.PSCredential]::Empty,
[Parameter( Mandatory=$false)]
[string]$XmlOutputFileName = [System.IO.Path]::GetTempFileName()+”.xml”
)
BEGIN {
$connection = New-Object -TypeName System.Data.SqlClient.SqlConnection
if (-not($PSBoundParameters.Credential)) {
$connectionString = “Server=$ServerInstance;Database=$Database;Integrated Security=True;”
}
else {
$connectionString = “Server=$ServerInstance;Database=$Database;Integrated Security=False;”
$userid= $Credential.UserName -replace ‘^.*\\|@.*$’
($password = $credential.Password).MakeReadOnly()
$sqlCred = New-Object -TypeName System.Data.SqlClient.SqlCredential($userid, $password)
$connection.Credential = $sqlCred
}
$connection.ConnectionString = $connectionString
$ErrorActionPreference = ‘Stop’
try {
$connection.Open()
Write-Verbose -Message “Connection to the $($connection.Database) database on $($connection.DataSource) has been successfully opened.”
}
catch {
Write-Error -Message “An error has occurred. Error details: $($_.Exception.Message)”
}
$ErrorActionPreference = ‘Continue’
$command = $connection.CreateCommand()
}
PROCESS {
$command.CommandText = $Query
$ErrorActionPreference = ‘Stop’
try {
$result = $command.ExecuteXmlReader()
}
catch {
Write-Error -Message “An error has occured. Error Details: $($_.Exception.Message)”
}
$ErrorActionPreference = ‘Continue’
if ($result) {
if($XmlOutputFileName) {
Write-Output “Creating file “+ $XmlOutputFileName + “with the output”
$encoding = [System.Text.Encoding]::UTF8
$writer = New-Object System.Xml.XmlTextWriter( $XmlOutputFileName, $encoding )
$writer.WriteNode($result, ‘true’)
$writer.Flush()
$writer.Close()
}
else{
Write $result.ReadOuterXml()
}
}
}
END {
$connection.Close()
}
}
Nice! is there a way to make multiple queries by using one connection? I have tried to use the following script but it didn’t work (it returned the first query only):
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = “Server=localhost;Database=master;Integrated Security=True”
$SqlConnection.Open()
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = @”
select host_name()
go
select @@version
go
“@
$SqlCmd.Connection = $SqlConnection
$results = $SqlCmd.ExecuteReader()
while ($results.Read())
{
$results.GetValue(0)
}
$SqlConnection.Close()
Many thanks
Love this function, but I’m having problems using it with the credential parameter. I can run the command successfully from a alternate user PowerShell prompt, but not using the same credentials with get-credential and the credential parameter. I get access denied errors even though it works from the runas prompt. Any ideas?