Capturing rowcount from invoke-sqlcmd

Question

I am using invoke-sqlcmd to pass queries to SQL Server and I would like to see the messages printed in the messages window of SSMS. When I do an insert from SSMS if successful I will see a message in the message window such as 1 row inserted. When I try this:

$res= invoke-Sqlcmd -Query "insert into accounts.dbo.payments values(25,12.50); print 'hey'" -ServerInstance "." -Verbose 4>&1
$res Verbose: Hey

How can I capture the string (1 row affected), which also appears in the message window?

Is there another way to pass commands to SQL Server that will enable me to capture all the resulting messages?

Answer 1

The row(s) affected message is not generated by SQL Server. It just passes the number back to the client, and it's up to the client whether/how to show it. That's just how SSMS chooses to show it.

You can do the same thing using -StatisticsVariable

$res = Invoke-Sqlcmd `
 -Query "insert into accounts.dbo.payments values(25,12.50); print 'hey'" `
 -ServerInstance "." `
 -StatisticsVariable stats `
 -Verbose 4>&1;
$res;
"$(stats.IduRows) row(s) affected";

This only gives overall statistics, whereas SSMS outputs the message for each DML statement executed. To get a similar result, you would probably have to use SqlCommand directly and handle the StatementCompleted event.

• RLDailey: those backticks are ... way too easy to miss. [grin] have you tried looking at Get-Help about_Splatting for an easier to read & easier to maintain technique?
• benjamin moskovits: When I look up invoke-sqlcmd I don't see -StatisticsVariable. What version of powershell has this parameter? When I run the code all I see is the verbose: Hey. When I remove the priint hey form the query $res is empty.
• Charlieface: Are you using SQLServer or SQLPS, it's only available in the newer SQLServer module, see learn.microsoft.com/en-us/powershell/sql-server/…
• Charlieface: @RLDailey I don't find splatting easier as it separates command and parameters. I beleive in some cases you can omit the backtick, but I prefer to be explicit
• RLDailey: i understand ... i disagree, but i DO understand. thank you for the feedback! [grin]