SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Powershell to get Results and Messages in SSMS inquiry


Powershell to get Results and Messages in SSMS inquiry

Author
Message
yhandz_21
yhandz_21
SSC Veteran
SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)

Group: General Forum Members
Points: 228 Visits: 309
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:

Joie Andrew
Joie Andrew
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24834 Visits: 2133
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
Thom A
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84205 Visits: 21586
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 :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
Shawn Melton
Shawn Melton
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22422 Visits: 3624
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: blog.wsmelton.info
yhandz_21
yhandz_21
SSC Veteran
SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)

Group: General Forum Members
Points: 228 Visits: 309
Hi,

See update above for expected output.
Shawn Melton
Shawn Melton
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22422 Visits: 3624
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: blog.wsmelton.info
yhandz_21
yhandz_21
SSC Veteran
SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)

Group: General Forum Members
Points: 228 Visits: 309
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search