Tips for working with Invoke-DbaQuery

[andreasjordan]

On Slack, people often ask how Invoke-DbaQuery can process multi-part scripts or commands with more than one result set. Here are a few examples.

But first I would like to introduce a few tools and give tips that every user of dbatools should know.

In general: Store the return of the commands in variables. Because otherwise the output of the commands simply ends up on the console, in exactly the way, that PowerShell thinks is right. It is quite possible that not all information is visible to the user. This information is then lost for the user.

And if you have stored the output of a command in a variable, better check again if it really contains the memory structure you expect. How? With this:

$myVariable | Get-Member
$myVariable | gm         # gm is the alias and saves a lot of keystrokes

Get-Member takes objects via the pipeline and shows not only the data type of the individual objects but also all properties and methods. But be careful: If $myVariable is an array or a collection, you will only get the information about the contained objects. If all objects are of the same type, you will get only one output and cannot conclude that $myVariable directly contains an object of this type. Therefore here is another possibility:

$myVariable.GetType()
$myVariable.GetType().FullName

GetType() is a method that is included in every class and that I also like to use in debug messages to show me the actual data type. This is a very good way to determine if $myVariable is a single object or an array.

Here's an example:

Import-Module dbatools

$database = Get-DbaDatabase -SqlInstance SRV1 -Database master
$database | Get-Member           # TypeName: Microsoft.SqlServer.Management.Smo.Database
$database.GetType().FullName     # Microsoft.SqlServer.Management.Smo.Database = The variable contains a database object

$database = Get-DbaDatabase -SqlInstance SRV1 -Database master, tempdb
$database | Get-Member           # TypeName: Microsoft.SqlServer.Management.Smo.Database
$database.GetType().FullName     # System.Object[] = It is an array of objects
$database.Count                  # 2 = The array contains two objects
$database[0].GetType().FullName  # Microsoft.SqlServer.Management.Smo.Database = The first element is a database object

If you are not sure whether the variable contains a single object or an array of objects, then you can use the foreach loop in both cases to have single objects inside the loop in any case. Have a look at the dbatools source code, we do it there all the time.

foreach ($db in $database) {
    $db.GetType().FullName  # Microsoft.SqlServer.Management.Smo.Database = The $db variable contains a database object
}

So much for preparation, now let's get to Invoke-DbaQuery. As a procedure that returns more than one result set, I will use sp_BlitzFirst from Brent Ozar's First Responder Kit here. If you don't have this procedure installed anyway, you can install it directly with a dbatools command:

Install-DbaFirstResponderKit -SqlInstance SRV1 -OnlyScript Install-Core-Blitz-No-Query-Store.sql

I use the rather new parameter -OnlyScript here, because this way I can also show you right away which script from the package you have to download from GitHub if you cannot use Install-DbaFirstResponderKit due to lack of internet access.

Ok, just now I recommended to always save the return and now this. But here I really don't care about the return.

I would like to use here the output of sp_BlitzFirst @SinceStartup = 1, which reliably returns four result sets in any situation. Why don't you try this command in SQL Server Management Studio for comparison.

$blitzFirstResult = Invoke-DbaQuery -SqlInstance SRV1 -Query 'sp_BlitzFirst @SinceStartup = 1'
$blitzFirstResult | Get-Member           # System.Data.DataRow
$blitzFirstResult.GetType().FullName     # System.Object[]
$blitzFirstResult[0].GetType().FullName  # System.Data.DataRow

So we get only the first table in the form of an array of rows.
To view the data similar to the view in SQL Server Management Studio, please use Out-GridView:

$blitzFirstResult | Out-GridView

The key to displaying the other result sets is in the parameter -As, which I will use in the following.

$blitzFirstResult = Invoke-DbaQuery -SqlInstance SRV1 -Query 'sp_BlitzFirst @SinceStartup = 1' -As DataRow
$blitzFirstResult | Get-Member           # System.Data.DataRow
$blitzFirstResult.GetType().FullName     # System.Object[]
$blitzFirstResult[0].GetType().FullName  # System.Data.DataRow

This is the default, so we get the same result.

$blitzFirstResult = Invoke-DbaQuery -SqlInstance SRV1 -Query 'sp_BlitzFirst @SinceStartup = 1' -As DataTable
$blitzFirstResult | Get-Member           # System.Data.DataTable
$blitzFirstResult.GetType().FullName     # System.Object[]
$blitzFirstResult[0].GetType().FullName  # System.Data.DataTable
$blitzFirstResult.Count                  # 4

Now we have an array of four elements, which are the four result sets that we also see in SQL Server Management Studio.

$blitzFirstResultTable1 = $blitzFirstResult[0]
$blitzFirstResultTable1 | Get-Member       # System.Data.DataRow
$blitzFirstResultTable1.GetType().FullName # System.Data.DataTable

The variable $blitzFirstResultTable1 contains an element of the type DataTable. If this object is passed to other commands via a pipeline, it will be split into individual objects of the type DataRow.
We can use this again for display with 'Out-GridView':

$blitzFirstResultTable1 | Out-GridView

A foreach loop also breaks the table into individual rows:

foreach ($row in $blitzFirstResultTable1) {
    $row.GetType().FullName  # System.Data.DataRow
}

There is one more option, but it is rarely used:

$blitzFirstResult = Invoke-DbaQuery -SqlInstance SRV1 -Query 'sp_BlitzFirst @SinceStartup = 1' -As DataSet
$blitzFirstResult | Get-Member           # System.Data.DataSet
$blitzFirstResult.GetType().FullName     # System.Data.DataSet

Here we have a single object of the type DataSet, which is not decomposed even by a pipeline. How do we get to the individual tables here? These are accessible via the Tables property.

$blitzFirstResult.Tables.GetType().FullName  # System.Data.DataTableCollection
$blitzFirstResult.Tables.Count               # Here are our four tables
$blitzFirstResultTable1 = $blitzFirstResult.Tables[0]
$blitzFirstResultTable1 | Get-Member         # System.Data.DataRow
$blitzFirstResultTable1.GetType().FullName   # System.Data.DataTable

Those were the options that start with Data*. But there are still PSObject and PSObjectArray, what do they do?

First of all: It is about the topic NULL. But since the output used so far does not contain NULL values, we now have to work with the output of sp_Blitz. Let's first run the output and take a look at the result:

$blitzResult = Invoke-DbaQuery -SqlInstance SRV1 -Query 'sp_Blitz' -As DataRow
$blitzResult | Out-GridView

The DatabaseName column is partially empty, so let's use that:

$emptyDatabase = $blitzResult | Where-Object -Property Priority -EQ -Value 0 | Select-Object -ExpandProperty DatabaseName
$emptyDatabase.GetType().FullName  # System.DBNull
$null -eq $emptyDatabase           # False

So we don't have $null here as we would expect in the PowerShell world, but we have an object of class System.DBNull here.
Now let's run the same query with the PSObject option:

$blitzResult = Invoke-DbaQuery -SqlInstance SRV1 -Query 'sp_Blitz' -As PSObject
$emptyDatabase = $blitzResult | Where-Object -Property Priority -EQ -Value 0 | Select-Object -ExpandProperty DatabaseName
$emptyDatabase.GetType().FullName  # Error: You cannot call a method on a null-valued expression.
$null -eq $emptyDatabase           # True

Here we now have the usual $null, so the GetType method fails.

And what type are the individual rows?

$blitzResult[0].GetType().FullName  # System.Management.Automation.PSCustomObject

So we are no longer dealing with lines but with objects. For further work this is (almost) irrelevant, but it still offers some advantages that I don't want to go into here for now. And what about PSObjectArray? Like DataRow PSObject returns only the rows of the first result set, but as PSCustomObject. PSObjectArray works similar to DataTable and returns an array with as many elements as there are (non-empty) result sets. Each element of this array contains an array with the individual rows of the result set as PSCustomObject.

So far for now. Questions and suggestions are welcome as comments to this discussion.

[andreasjordan]

As the article is currently featured on twitter, let me add a quick note about -As SingleValue. This is super helpful if you just need one column in a query:

$databaseNames = Invoke-DbaQuery -SqlInstance SRV1 -Query 'SELECT name FROM sys.databases' -As SingleValue

The returning object in this case is an array of strings - so we can simply work with in a foreach loop for example.

[andreasjordan]

In case you want to have a deeper look at what the code does: You call Invoke-DbaQuery, but the main part of the work is done inside of Invoke-DbaAsync:
https://github.com/dataplat/dbatools/blob/development/functions/Invoke-DbaQuery.ps1
https://github.com/dataplat/dbatools/blob/development/internal/functions/Invoke-DbaAsync.ps1