The preferred method to execute a query from a .NET language including Powershell is to use ADO.NET which requires creating a DataSet, then creating a DataAdapter, and finally filling the DataAdapter. For most data retrieval needs from scripts ADO.NET can be a little too heavy. Fortunately there are several ways to make this task simpler and still retain the benefits of .NET DataTables. Let's look at three methods to get SQL Serer data from Powershell. Note: You will need SMO installed which is included with SQL Server Management Studio.

Method 1: SMO ExecuteWithResults

The SMO method ExecuteWithResults returns a  DataSet object representing a collection of DataTables. This method is much easier to use than ADO.NET. For example, the following code creates a database object on the pubs database and executes a query from Powershell:

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$srvr = new-object ("Microsoft.SqlServer.Management.Smo.Server") "Z002\SqlExpress"
$db = $srvr.Databases["pubs"]
$db.ExecuteWithResults("SELECT * FROM dbo.authors")

Pros: Does not require installation of additional items other than SMO.

Cons: A bit more complex than other methods.

Recommendation: Use SMO ExecuteWithResults when you want to build scripts/functions which are not dependent on additional installations.

Method 2: SQL Server 2008 Powershell Cmdlet Invoke-SqlCmd

The SQL Server 2008 Powershell provider cmdlet Invoke-Sqlcmd and also returns a DataTable. For example, the following code executes Invoke-Sqlcmd from the Sqlps host:

Invoke-Sqlcmd -Query "SELECT * FROM pubs..authors" -ServerInstance "Z002\SqlExpress"

Pros: Simple syntax, supports a wide range of options including XQuery

Cons: Requires installation of SQL 2008 Powershell host (sqlps.exe)

Recommendation: Use Invoke-SqlCmd if you have SQL Server 2008 Powershell host (sqlps.exe) installed.

Method 3: CodePlex SQL Server Powershell Extensions function Get-SqlData

The CodePlex project SQL Server Powershell Extensions (SQLPSX) provides a simple function called Get-SqlData. The function is a variation of Method 1 with error handling and some modularization. The following example executes the SQLPSX Get-SqlData function.

Get-SqlData "Z002\SqlExpress" pubs "SELECT * FROM dbo.authors"

SQLPSX also provides a Set-SqlData function for running statements which do not return data sets i.e. updates, inserts, deletes, etc.

Pros: Simple syntax, implements some basic error checking

Cons: Requires sourcing SQLPSX Library

Recommendation: Use Get-SqlData or write your own function when you want to implement a solution not dependent on the SQL 2008 Powershell host and you want to abstract some of the complexity of Method 1.

Things to do with a DataTable

Once you have your data into a DataTable you can do a number of things including piping the output to one of the built-in cmdlets to produce an html report or csv file. For example, the following code executes a query and produces nicely formatted HTML output:

Get-SqlData "Z002\SqlExpress" pubs "SELECT * FROM dbo.authors" | ConvertTo-Html

Or take, for example the following which sends the output to a CSV file:

Get-SqlData "Z002\SqlExpress" pubs "SELECT * FROM dbo.authors" | Export-Csv -path ./authors.csv

You can also apply a filter to the DataTable by piping the output to where:

Get-SqlData "Z002\SqlExpress" pubs "SELECT * FROM dbo.authors" | where { $_.au_lname -eq 'White' }

Using one of the three methods you can quickly build scripts. For example, save the following to a file named as lastbackup.ps1:

param($server,$dbname)
$scriptRoot = Split-Path (Resolve-Path $myInvocation.MyCommand.Path)
. $scriptRoot\LibrarySmo.ps1

$qry = @"
SELECT d.name as 'DBName', MAX(ISNULL(DATEDIFF(dd,ISNULL(b.backup_start_date, '01/01/1900'),GETDATE()),0)) AS 'NumDays'
FROM master..sysdatabases d
LEFT JOIN msdb..backupset b
ON d.name = b.database_name
WHERE b.type IN ('D','I')
AND d.name = '$dbname'
GROUP BY d.name
"@
Get-SqlData $server 'msdb' $qry

Now execute your new script passing in the server and database name to get the number of days since the last database backup:

./lastbackup.ps1 'Z002\SqlExpress' pubs

As demonstrated in the article SQL Server PowerShell Extensions (SQLPSX) Part 1 you can use Get-SqlData or any of the three methods to get a list of SQL Server instances from a table and pipe the output to additional commands. For example, the following code obtains a list of SQL Server instances from a table and then retrieves  version information:

Get-SqlData 'Z002\SqlExpress' SQLPSX "SELECT Server FROM dbo.SqlServer" | foreach { $srvr = Get-SqlServer $_.Server ; $srvr.Information | add-Member -memberType noteProperty -name Server -value $srvr.Name -passThru | Select Server, VersionString }

Since each of the three methods return a DataTable, you can use the .NET 2.0 class, SqlBulkcopy to copy a table from one SQL instance and database to another:

$dataTable = Get-SqlData "Z002\SqlExpress" pubs "SELECT * FROM dbo.authors"
$connectionString = "Data Source=Z002\Sql2K8;Integrated Security=true;Initial Catalog=pubs;"
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
$bulkCopy.DestinationTableName = "authors"
$bulkCopy.WriteToServer($dataTable)

The DataTable method is such a useful technique you may find using Powershell scripts to copy data from one identical table to another rather than using something as heavy weight as SSIS to be your new preferred method.

Conclusion

This article demonstrated three methods you can use to query SQL Server data from Powershell. These methods produce a DataTable as output which be combined with built-in cmdlets, piped to additional commands and used with the SqlBulkCopy class. Using one of the three methods you should be able to easily query tables from Powershell and build scripts.