Skip to main content

 none
SqlPS Invoke-SqlCmd stops all output if the first SELECT in a batch returns now rows RRS feed

  • Question

  • I just lost half my day chasing this insane bug.  I tried upgrading to the recommended SqlServer powershell module to see if it fixes it, but all that seemed to do was break both SqlPS and SqlServer modules.

    In the case where you have a script that contains multiple PRINT and SELECT statements, and the first SELECT returns no rows, all following SELECTs and PRINTs are silently ignored.

    For example:

    >> Invoke-Sqlcmd -ServerInstance . -Database 'pureprod' -query "
    >>  PRINT 'firstprint'
    >>  SELECT 'firstselect' where 0 = 1
    >>  PRINT 'secondprint'
    >>  SELECT 'secondselect'
    >>  PRINT 'thirdselect'
    >>  SELECT 'thirdselect'
    >>  PRINT 'fourthprint'
    >>  " -verbose

    gives just

    VERBOSE: firstprint

    Monday, September 30, 2019 9:03 PM

All replies

  • After adding GO keyword after every statement, I got the desired output:

    PRINT 'firstprint'
    go
    SELECT 'firstselect' where 0 = 1
    go
    PRINT 'secondprint'
    go
    SELECT 'secondselect'
    go
    PRINT 'thirdselect'
    go
    SELECT 'thirdselect'
    go
    PRINT 'fourthprint'
    go


    Cheers
    Vaibhav
    MCSA (SQL Server 2014)

    Tuesday, October 1, 2019 6:54 AM
  • That's nice that adding GO works around the bug, but it's still a failure.  This bug is terribly dangerous because it means Invoke-SqlCmd is giving the user incorrect results for valid SQL.

    Invoke-SqlCmd should execute valid SqlCmd files and return the results.   Presently, it does not.

    Wednesday, October 16, 2019 6:36 PM