Search code examples
sqlsql-serverpowershelldbatools

How to return multiple recordsets from stored procedure using PowerShell

I need to run a stored procedure that return 2 result sets with PowerShell. I use dbatools to do so but I could use .NET to get there. I just don't know how.

For this example, I use exec sp_spaceused that will return the space used in the actual database. Here's the result in SSMS:

result from sp_spaceused in SSMS

As you can see here, there are 2 result sets. Now when I run the same command in PowerShell, I can't figure how to get the next result set.

Here is the code I've come up with:

$conn = Connect-DbaInstance -SqlInstance . -MultipleActiveResultSets
$query = 'exec sp_spaceused'
Invoke-DbaQuery -SqlInstance $conn -Query $query

I'm not even sure if I used MultipleActiveResultSets in the right way. I can't find any good example anywhere.

Solution

  • Wow, I just found the answer by testing all the different -As options. Here's the code:

    $conn = Connect-DbaInstance -SqlInstance . -Database 'StackOverFlow'
$query = 'exec sp_spaceused'
$ds = Invoke-DbaQuery -SqlInstance $conn -Query $query -As DataSet
foreach ($table in $ds.Tables) {
    $table | Out-String
}

    I use Out-String to avoid joining objet but you could use Out-GridView. I also realize that I don't need to use -MultipleActiveResultSets.