Powershell to get Results and Messages in SSMS inquiry

Question

Post reply

Powershell to get Results and Messages in SSMS inquiry

yhandz_21

SSC-Addicted

Points: 446
More actions
September 19, 2017 at 9:08 pm

#323577

I have a powershell script that will execute a query on a database that has Replication.
$QueryPath = "C:\Replication\RepAsPub.sql" $OutputFile = "C:\Replication\RepAsPub_Result.csv"
$ExecuteQuery = Get-Content -Path $QueryPath -Raw Get-Content -Path "C:\Replication\Instance_List.txt" | ForEach-Object { Invoke-SqlCmd -ServerInstance $_ -Query $ExecuteQuery -Queryimeout 60000 } | Export-Csv -NoTypeInformation -Path $OutputFile
SSMS output. Results and Messages:

But on the outputs on the csv file it is only showing the Results:

Need help that it will also have the Messages shown on a third column. (It should also catch in case there are errors)
Expected output should be, see screenshot below:

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 1

If you want the row count can't you just add "SET NOCOUNT OFF" in your query argument that is passed?

Joie Andrew
"Since 1982"

Thom A SSC Guru Points: 99133 More actions · Answer 2

SQLCMD does return the number of rows. At least Version 13 on Linux does:
[username]@ihy:~$ sqlcmd -S localhost -U [Username] -Q "SELECT @@ServerName AS ServerName;" Password: ServerName -------------------------------------------------------------------------------------------------------------------------------- ihy

(1 rows affected)
I did also give it a quick go on my Windows laptop and got the same (I think that has Version 13 as well, if not 12).

Could you be a bit more specific?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 3

yhandz_21 - Tuesday, September 19, 2017 9:08 PM
Is there a powershell script that will show both output Results and Messages.
For example:
Select @@servername
It should show on the powershell output
Result - Server/InstanceName
Messages- (1 row(s) affected

Basic answer: No.

Bit more detailed: The "message" tab you get in SSMS for row counts and print messages is a separate stream that is not as easily accessible in PowerShell. It can be accessed but not with one or two simply commands. You can see how you can do this in this post: here.

In order to get the example output you have provided you would have to generate that yourself using `Write-Output`, passing in the desired output after you captured it.

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

yhandz_21 SSC-Addicted Points: 446 More actions · Answer 4

yhandz_21

SSC-Addicted

Points: 446

September 25, 2017 at 8:59 pm

#1960876

Hi,

See update above for expected output.

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 5

yhandz_21 - Monday, September 25, 2017 8:59 PM
Hi,
See update above for expected output.

In PowerShell and .NET world that third column of data you are seeking does not exist. SSMS is generating that information for you.

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

yhandz_21 SSC-Addicted Points: 446 More actions · Answer 6

Shawn Melton - Monday, September 25, 2017 1:51 AM
yhandz_21 - Tuesday, September 19, 2017 9:08 PM
Is there a powershell script that will show both output Results and Messages.
For example:
Select @@servername
It should show on the powershell output
Result - Server/InstanceName
Messages- (1 row(s) affected
Basic answer: No.
Bit more detailed: The "message" tab you get in SSMS for row counts and print messages is a separate stream that is not as easily accessible in PowerShell. It can be accessed but not with one or two simply commands. You can see how you can do this in this post: here.
In order to get the example output you have provided you would have to generate that yourself using `Write-Output`, passing in the desired output after you captured it.

can you help me to add that write output. I tried following the blog but isn't successful. or you can help adding error handling with the code