Interacting with SQL Databases in PowerShell: Invoke-SqlCommand

Jeffrey McManus recently wrote about database queries with PowerShell – a small script that lets you query a SQL data store. This is really powerful. Rather than context switch into SQL Express (or TOAD, or your other favourite administration tool,) you can do what you need from PowerShell.


It goes even further, though. A lot of PowerShell’s built-in commands have a set-oriented flavour: Where-Object, Select-Object, Group-Object, and Sort-Object. I also blogged about a set intersection script here: http://www.leeholmes.com/blog/CreatingSQLsJoinlikeFunctionalityInMSH.aspx. After spending a ton of time in Oracle databases during an internship at General Electric, I remember always wishing that DIR supported a WHERE clause. Well, with PowerShell, it does!


One thing Jeffrey’s post doesn’t fully highlight is that the .NET Framework can also return fully structured objects that represent the results of your query. If your SELECT query returns a recordset with Name and Address columns, you can get back objects with Name and Address properties. With that, you can slice and dice the results even further in the shell.



PS D:\Temp> Invoke-SqlCommand.ps1 -Sql “SELECT TOP 5 * FROM Orders” | Format-Table


    OrderID CustomerID   EmployeeID OrderDate   RequiredDat ShippedDate     ShipVia     Freight
                                                e
    ——- ———-   ———- ———   ———– ———–     ——-     ——-
      10248 VINET                 5 7/4/1996… 8/1/1996… 7/16/199…           3       32.38
      10249 TOMSP                 6 7/5/1996… 8/16/199… 7/10/199…           1       11.61
      10250 HANAR                 4 7/8/1996… 8/5/1996… 7/12/199…           2       65.83
      10251 VICTE                 3 7/8/1996… 8/5/1996… 7/15/199…           1       41.34
      10252 SUPRD                 4 7/9/1996… 8/6/1996… 7/11/199…           2        51.3


The .NET Framework supports more than just SQL servers, though. It supports Access databases and Excel workbooks, too. Their connection strings are a black-art, so it is natural to wrap all of that magic in a script.


So natural that I include in the upcoming PowerShell Cookbook: Invoke-SqlCommand. (PS: Expect some good news about the book VERY soon.)



##############################################################################
##
## Invoke-SqlCommand.ps1
##
## From Windows PowerShell Cookbook (O’Reilly)
## by Lee Holmes (http://www.leeholmes.com/guide)
##
## Return the results of a SQL query or operation
##
## ie:
##
## ## Use Windows authentication
## Invoke-SqlCommand.ps1 -Sql “SELECT TOP 10 * FROM Orders”
##
## ## Use SQL Authentication
## $cred = Get-Credential
## Invoke-SqlCommand.ps1 -Sql “SELECT TOP 10 * FROM Orders” -Cred $cred
##
## ## Perform an update
## $server = “MYSERVER”
## $database = “Master”
## $sql = “UPDATE Orders SET EmployeeID = 6 WHERE OrderID = 10248″
## Invoke-SqlCommand $server $database $sql
##
## $sql = “EXEC SalesByCategory ‘Beverages'”
## Invoke-SqlCommand -Sql $sql
##
## ## Access an access database
## Invoke-SqlCommand (Resolve-Path access_test.mdb) -Sql “SELECT * FROM Users”
##
## ## Access an excel file
## Invoke-SqlCommand (Resolve-Path xls_test.xls) -Sql ‘SELECT * FROM [Sheet1$]‘
##
##############################################################################

param(
[string] $dataSource = “.\SQLEXPRESS”,
[string] $database = “Northwind”,
[string] $sqlCommand = $(throw “Please specify a query.”),
[System.Management.Automation.PsCredential] $credential
)

## Prepare the authentication information. By default, we pick
## Windows authentication
$authentication = “Integrated Security=SSPI;”

## If the user supplies a credential, then they want SQL
## authentication
if($credential)
{
$plainCred = $credential.GetNetworkCredential()
$authentication =
(“uid={0};pwd={1};” -f $plainCred.Username,$plainCred.Password)
}

## Prepare the connection string out of the information they
## provide
$connectionString = “Provider=sqloledb; “ +
“Data Source=$dataSource; “ +
“Initial Catalog=$database; “ +
“$authentication; “

## If they specify an Access database or Excel file as the connection
## source, modify the connection string to connect to that data source
if($dataSource -match ‘\.xls$|\.mdb$’)
{
$connectionString = “Provider=Microsoft.Jet.OLEDB.4.0; Data Source=$dataSource; “

if($dataSource -match ‘\.xls$’)
{
$connectionString += ‘Extended Properties=”Excel 8.0;”; ‘

## Generate an error if they didn’t specify the sheet name properly
if($sqlCommand -notmatch ‘\[.+\$\]‘)
{
$error = ‘Sheet names should be surrounded by square brackets, and ‘ +
‘have a dollar sign at the end: [Sheet1$]‘
Write-Error $error
return
}
}
}

## Connect to the data source and open it
$connection = New-Object System.Data.OleDb.OleDbConnection $connectionString
$command = New-Object System.Data.OleDb.OleDbCommand $sqlCommand,$connection
$connection.Open()

## Fetch the results, and close the connection
$adapter = New-Object System.Data.OleDb.OleDbDataAdapter $command
$dataset = New-Object System.Data.DataSet
[void] $adapter.Fill($dataSet)
$connection.Close()

## Return all of the rows from their query
$dataSet.Tables | Select-Object -Expand Rows



 

7 Responses to “Interacting with SQL Databases in PowerShell: Invoke-SqlCommand”

  1. Jay Bazuzi [MS] writes:

    Cool.

    In C# 3.0, you can write C# langauge queries and have them resolved on the server. It might be interesting to use the Inline C# script to write Linq queries to SQL, so you don’t have to write SQL queries.

    Now, if only there was Linq for PowerShell :-)

  2. Steve Hienr writes:

    Thank you, thank you, thank you.

    I already wrote a couple scripts to call that one so I don’t have to open the management studio again to do stuff I often do multiple times a day. You just saved be a bunch of time (and memory).

    I think I’m going to have fun thinking of things to do with this script.

  3. Andrey Balaguta writes:

    Very handy script, thanks a lot. I use it for Oracle interaction (with small changes made to use OraOLEDB.Oracle provider). But there is one problem with big data arrays — script caches rows inside, them gives them all in once, which is of course freezing powershell and is of no use (the problem reminds me Get-Content behaviour on big files). If it would be possible to give one row at a time, so I can view "data flow" and terminate it with Ctrl-C (then do some restrictions, run Invoke-SqlCommand again, etc)…

  4. Kirsten Whitworth writes:

    I am a novice learning both PowerShell and .NET by example. I bought your book late last year, and have devoured it. Thanks!

    I am using a customized version of your Invoke-SqlCommand.ps1 function to determine whether the results of two SQL queries are equal, but have discovered that System.Data.DataSet objects do not behave as I expected. Could you point me in the right direction, please?

    I am comparing identical tables in separate database instances. I know I am getting back the correct data, because I have compared it with manual SQL queries. However, the dataset objects appear to be NULL by the time I can assert that they are equal. Do I need to create a data reader and "copy" the data into non-dataset objects before I may assert their equality? What am I missing?

  5. PowerShell for the SQL DBA « Iain Simpson's TechJazz writes:

    [...] Interacting with SQL Databases in PowerShell: Invoke-SqlCommand http://www.leeholmes.com/blog/2007/10/19/interacting-with-sql-databases-in-powershell-invoke-sqlcomm… [...]

  6. Calagan writes:

    Thanks for this very useful script.

    I sucessfully ran an SQL SELECT query that produce one column with a long list of user account names.

    I am trying run a Get-QADUser cmdlet against all these users using a Foreach loop in the snippet below, but I can’t seem to get the syntax right because it’s producing Get-QADUser “System.Data.DataRow” instead of Get-QADUser .

    $objects = (.\Invoke-SqlCommand -Sql $sql)
    ForEach ($object in $objects) {Get-QADUser $object}

    Thanks in advance to whoever can help.

  7. Powershell Scripting Task | jhelsalta.co.uk writes:

    [...] some useful example on how to code the script to be able to connect and retrieve the data in SQL. http://www.leeholmes.com/blog/category/guide/ . All I did is changed the ftp server, password, connection string and username to be able to used [...]

Leave a Reply