Invoke-SqlCmd stops returning output if first query returns no rows

[Pxtl]

Using invoke-sqlcmd in SqlServer 22.2.0, under powershell 5.1, execute the following:

Invoke-SqlCmd -TrustServerCertificate "
  print 'print one (SKIPME query)' 
  select 'query one SKIPME' as colOne WHERE 0=1
  print 'print two'
  select 'query two' as colTwo
"

This yields no results. With -verbose enabled we see only

VERBOSE: print one (SKIPME query)

showing how the output terminates after the first query.

Note that if the empty-result query is later in the script, this behavior does not happen.

>> Invoke-SqlCmd -TrustServerCertificate "
>>   print 'print one'
>>   select 'query one' as colOne
>>   print 'print two'
>>   select 'query two' as colTwo
>>   print 'print three (SKIPME query)'
>>   select 'query three SKIPME' as colThree where 0=1
>>   print 'print four'
>>   select 'query four' as colFour
>> " -Verbose
VERBOSE: print one

VERBOSE: print two
VERBOSE: print three (SKIPME query)
VERBOSE: print four
colOne
------
query one

Now, in the above example, we don't see queries two and four because they have different columns and Powershell's pipeline will base its column choice on the first result, however, we still see the VERBOSE logs from after query three.

And we can work-around Powershell's bad behavior with varying column names by using the -outputAs DataTables mode:

>> Invoke-SqlCmd -TrustServerCertificate "
>>   print 'print one'
>>   select 'query one' as colOne
>>   print 'print two'
>>   select 'query two' as colTwo
>>   print 'print three (SKIPME query)'
>>   select 'query three SKIPME' as colThree where 0=1
>>   print 'print four'
>>   select 'query four' as colFour
>> " -ErrorAction "Stop" -OutputAs "DataTables" -Verbose | ForEach-Object {
>>         if($_.GetType() -eq [Data.DataTable]) {
>>             #format datatables as tables
>>             $_.rows | Format-Table | Out-String -Stream -Width $fileWidth
>>         } else {
>>             #all other data written as raw text
>>             $_
>>         }
>>     }
VERBOSE: print one

colOne
------
query one

VERBOSE: print two

colTwo
------
query two

VERBOSE: print three (SKIPME query)
VERBOSE: print four

colFour
-------
query four

So here everything works! Hooray! But only if the where 0=1 falls later in the script. If it's a first-query we still get the old bad behavior:

>> Invoke-SqlCmd -TrustServerCertificate "
>>   print 'print one (SKIPME query)'
>>   select 'query one SKIPME' as colOne WHERE 0=1
>>   print 'print two'
>>   select 'query two' as colTwo
>> " -ErrorAction "Stop" -OutputAs "DataTables" -Verbose | ForEach-Object {
>>         if($_.GetType() -eq [Data.DataTable]) {
>>             #format datatables as tables
>>             $_.rows | Format-Table | Out-String -Stream -Width $fileWidth
>>         } else {
>>             #all other data written as raw text
>>             $_
>>         }
>>     }
VERBOSE: print one (SKIPME query)

[Matteo-T]

Hi @pxtl, thanks for reporting the issue.

I looked into it and I have a fix for the issue you reported that I hope to push out in the upcoming v22.3.

I've also observed that there's a bit of inconsistencies today when it comes to Multiple Result Sets when one of the result set is empty (namely: what should be emitted? no data? an empty table/dataset? Or perhaps just skipping the empty sets?) that would be nice to address in the next major update (v23). Personally, I'd be inclined in making the result uniform and go with:

• When the result set is empty, just ignore it: no data is emitted, no empty table is emitted, no empty dataset (aka a data set where alll the tables are empty)
• Name the tables in such a way that they map to the result set, so there would be a possibility to know what result set maps to what query.
• Maybe, allow the user to pass a -DoNotIgnoreEmptyResultSets or something like that that would result in generating empty objects (tables and datasets) to have some kind of more static result [and, ultimately, a behavior similar to what we have today]

Of course, that can only be done in v23.x, because it would be quite a bit breaking change. What do you think?

For v22.3, I'll just try to minimize the chances of breaking changes, which should be enough to unblock you.