On the dbatools community 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.
Two tools: Get-Member and GetType()
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:
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.
One note before the first command runs against an instance: since version 2.0, dbatools builds the connection encrypted by default and only trusts certificates issued by a recognised certificate authority. Against a test instance that still uses the self-signed certificate from the installation, all of the following examples therefore fail with the message "The certificate chain was issued by an authority that is not trusted". The command Set-DbatoolsInsecureConnection restores the former behaviour; the documentation describes which settings it changes.
Here's an example:
If you are not sure whether the variable contains a single object or an array of objects, then you can use a foreach loop in both cases, so that you always have single objects inside the loop. Have a look at the dbatools source code, we do it there all the time.
One procedure, four result sets
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 the kit installed anyway, you can install the required procedures directly with a dbatools command:
I use the -OnlyScript parameter here, because this way I can also show you right away which scripts from the package you have to download from GitHub if you cannot use Install-DbaFirstResponderKit due to lack of internet access. Besides sp_BlitzFirst we will need sp_Blitz further down, and since version 8.34 of the kit from July 2026 that requires the procedure sp_ineachdb: if it is missing, every call to sp_Blitz fails with the message that the stored procedure dbo.sp_ineachdb could not be found. Which values the parameter accepts is dictated by the First Responder Kit – the list moves with the kit and is shown in the help for the command.
I would like to use the output of sp_BlitzFirst @SinceStartup = 1 here, which returns four result sets with this parameter, but only one without it. I checked this with version 8.34 of the kit; how many result sets a third-party procedure returns can change with every new version. Why don't you try this command in SQL Server Management Studio for comparison.
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
Out-GridView is only available on Windows. On Linux and macOS, Out-ConsoleGridView from the Microsoft.PowerShell.ConsoleGuiTools module does the same job, though inside the terminal instead of in a window of its own.
DataRow, DataTable and DataSet
The key to displaying the other result sets is in the parameter -As, which I will use in the following.
This is the default, so we get the same result.
Now we have an array of four elements, which are the four result sets that we also see in SQL Server Management Studio.
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:
There is one more option, but it is rarely used:
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.
PSObject and PSObjectArray: the NULL question
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 procedure and take a look at the result:
The DatabaseName column is partially empty, so let's use that:
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:
Here we now have the usual $null, so the GetType method fails.
And what type are the individual rows?
So we are no longer dealing with rows but with objects. For further work this is (almost) irrelevant, but it has two tangible advantages: empty values can be compared with $null as everywhere in PowerShell, and the objects carry no baggage. With ConvertTo-Json, a DataRow drags along the properties RowError, RowState, Table, ItemArray and HasErrors, a PSCustomObject only its columns.
And what about PSObjectArray? First a difference that is easy to miss: unlike DataRow, PSObject is not limited to the first result set, but returns the rows of all result sets one after the other in one flat array, with no separation between the result sets. With sp_BlitzFirst @SinceStartup = 1 that is the rows of all four tables. PSObjectArray works similarly to DataTable and returns an array with as many elements as there are (non-empty) result sets. Empty result sets are therefore missing from it, unlike in the DataSet, and the following ones move up. Each element of this array contains an array with the individual rows of the result set as PSCustomObject. With one exception: if a result set has only one row, its place is taken not by an array but by the single PSCustomObject, because PowerShell does not wrap a single object in an array on its own. With sp_BlitzFirst this affects the fourth result set, and that is exactly what the look with GetType() from the beginning of this text is for.
SingleValue
That leaves the sixth value, and it is the odd one out: SingleValue. All the options so far were about getting at more – the second result set, the individual rows. This one is about the opposite. Some queries return exactly one value, and then going through table and row is just a detour.
Without -As SingleValue the same query comes back as a row, and for me to get hold of the value at all, the column needs a name as well:
Both ways lead to the same value, but the first one says in the line itself what is meant. You would expect SingleValue to deliver exactly what the name says: a single value, the first column of the first row. That is not quite the case, and since we have just been dealing with NULL and with the difference between a single object and an array, a closer look is worthwhile here once more:
So SingleValue returns the first column of all rows of the first result set: with exactly one row the value itself, with several rows an array, with no row $null, and a NULL arrives as System.DBNull. Further columns and further result sets are dropped by the parameter without any warning. The name is therefore a promise you make, not one the command checks: whoever sets SingleValue declares that the query returns exactly one value. Typical cases are COUNT(*), SELECT @@SERVERNAME or reading a single configuration setting.
Conclusion
The -As parameter decides which structure Invoke-DbaQuery returns: DataRow for the rows of the first result set, DataTable and DataSet for all result sets, PSObject and PSObjectArray for PowerShell objects without DBNull, SingleValue for the one value you promised. If you check with Get-Member and GetType() what is in the variable, none of the six variants will surprise you.
If you want to know what Invoke-DbaQuery does on the inside along the way — how the string "SRV1" turns into a connection, and why several calls share the same one — you will find that in my article "dbatools in detail: What happens when Invoke-DbaQuery is used?". Questions and suggestions are welcome as comments on this post.
If you want to build or extend your SQL Server automation with dbatools, get in touch. We are happy to help.