Hi,I've been banging my head against the wall trying to get the invoke-sqlcmd powershell cmdlet to correctly execute my stored procedures.Consider the following:CREATE PROCEDURE testASBEGIN -- SELECT 'A' SELECT 'B' WHERE 1=2 SELECT 'C'END
Which when executed using sqlcmd.exe:C:\Program Files\Microsoft SQL Server\100\Tools\Binn>SQLCMD.EXE -s server -d db -Q "exec test"--CC:\Program Files\Microsoft SQL Server\100\Tools\Binn>
Returns a blank recordset then 'C' as expected.But when executed using invoke-sqlcmd:PS C:\> Invoke-Sqlcmd -ServerInstance server -Database db -Query "EXEC test"PS C:\>
Doesn't return anything. I believe the blank recordset is screwing up the output somehow, preventing anything after the select statement from being displayed.Interestingly, if you output a non-blank recordset before the blank recordset, everything else runs ok. I.e, if you uncomment the line SELECT 'A' statement in the procedure, the output is now:PS C:\> Invoke-Sqlcmd -ServerInstance server -Database db -Query "EXEC test"Column1-------ACPS C:\>
This has been driving me nuts as I want to output an error if a blank recordset is returned. But with invoke-sqlcmd behaving like this, the error doesn't get returned.I've verified that the code after the blank recordset does get executed by putting in a few update statements after it, it just appears to stop outputting to screen.Does anyone else experience this problem and/or have a workaround/explanation? Is it a bug?Thanks
invoke-sqlcmd bug?
Posted - 2009-09-16 : 21:03:53
