invoke-sqlcmd fails shows no results on certain type of queries

Question

Invoke-Sqlcmd -ServerInstance '.' -Database 'MyDB' 
      -Query 'EXEC SprocA  @param1= "value";EXEC SprocB  @param1= "value";'

Basically I have my Invoke-SqlCmd running a query that invokes two stored procedures. Both the stored procedures output a bunch of rows.

However if sprocA does not output any results (empty select results or no rows), then the invoke command does not seem to print the output of the second sprocB even if it has data.

If I change the order of the stored procedures in my Invoke-SqlCmd commands query parameter, then this works perfectly and returns the output of the first stored procedure.

If I had three stored procedure calls where the first returns data and the second does not and the third does, it prints output of the first result and third result.

Basically it does not print any output only if the first stored procedure has no output. Seems weird.

Anything I can do to get around this SQL wise ? Could be a PowerShell thing?

I was also able to repro this with two Select statements where one returns data and the other does not.

Answer 1

This is the documented behavior of invoke-sqlcmd

When this cmdlet is run, the first result set that the script returns is displayed as a formatted table. If subsequent result sets contain different column lists than the first, those result sets are not displayed. If subsequent result sets after the first set have the same column list, their rows are appended to the formatted table that contains the rows that were returned by the first result set.

It looks like both result sets are actually returned, but not output by defaut.

EG

   PS C:\> Invoke-SqlCmd "select 1 a; select 2 b, 3 c;" | % { $_ | Out-Default }

outputs

a
-
1



b c
- -
2 3

Answer 2

Actually, it is hard to believe MS made such a mistake or maybe it's not a mistake. Whatever, when you run Invoke-SqlCmd and with a query like the following,

select * from table1 where id = 1111 -- non-exists id, this select returns nothing
select * from table2 where id = 2222 -- exists id, this select returns something

On SSMS you can see 2 result sets, the first one is empty.

However, the Invoke-SqlCmd doesn't return anything when the first result set is empty, even other result sets are not. My face was like ?_?

Another approach is to write you own invoke SQL function like following to return whatever result sets, even the empty ones.

    $sql_Conn = New-Object System.Data.SqlClient.SQLConnection
    $sql_Conn.ConnectionString = $sqlConnectionString
    $sql_Conn.Open()
    $sql_cmd = New-Object system.Data.SqlClient.SqlCommand($Query, $sql_Conn)
    $sql_ds = New-Object system.Data.DataSet
    $sql_da = New-Object system.Data.SqlClient.SqlDataAdapter($sql_cmd)
    [void]$sql_da.fill($sql_ds)
    $sql_Conn.Close()
    return $sql_ds

Everything is fine until a new problem comes about the keyword GO in your script, you must know it if you use SSMS. The thing is, this GO is not a SQL command. it is just a separator used by SSMS. MS developed codes can handle the GO in a good manner, e.g. SSMS, Invoke-SqlCmd and sqlcmd.exe. If you use your own SQL invoke function you will get syntax issue

Incorrect syntax near 'GO'

While people most likely to ask you to update the SQL script to remove all GO lines, however, things are not always under controlled, normally need to work with different people and teams.

At last, I have to trim the GO in my scripts like the following

$Query = $Query -ireplace "(^|\r|\n)[ \t]*\bGO\b[ \t]*(\r|\n|$)", '$1$2'

https://github.com/LarrysGIT/Invoke-Sql

Of course, the story is not over, the more I am trying to automate SQL related tasks. The more issue found. There are multiple ways to automatically execute SQL script. None of them are perfect so far.

Invoke-Sql (My own script)

* Is able to handle the key separator 'GO'
* Is able to handle duplicate columns
* Fully support multiple result sets, even the first result set is empty
* Unable to handle `Create or alter` keywords if there are contents ahead
* Unable to handle special characters like '194 160' (non-breaking space) in SQL script (edited by some document edit tool, MS word for example)

Invoke-SqlCmd

* Is able to handle the key separator 'GO'
* Is able to handle special characters like 'non-breaking space'
* Unable to handle duplicate columns
* Unable to fully handle multiple result sets (when the first table is empty)

sqlcmd.exe

* Is able to handle all things (briefly tested)
* The returned result sets are plain text, hard to parse

Microsoft.SqlServer.SMO snapin

* The API of SQL server management studio
* Theoretically should be able to handle all cases
* Need to dig more