Powershell - Output SQL Messages to file

Question

I have a Powershell script that invokes a saved SQL Query file and runs it on a specific Server & Database. That part is working well, the issue is that I would like to save the SQL Messages that it generates to a log file (see picture).

SQL Output from after Query is run

This is not working with my current code, and I believe that's because since it's technically not Query output but instead reindexing and updating tables, not fetching data.

My current relevant code is:

{    
Import-Module SQLPS         
$Data = Invoke-Sqlcmd -InputFile $SQLQuery -ServerInstance $Server -Database $Database -QueryTimeout 0    
$Data | out-file "$Output$Database$date.txt"    
}

But that just generates an empty text file. I'm looking to get the info on rebuilding indexes and the updates it's doing saved off into a different file through Powershell. You can do this through SSMS by right clicking in the Messages window and clicking "Save Results As..." but looking to include this in my automation since it's running as a Scheduled Task and no one is on SSMS.

Powershell v3/Windows Server 2012/SQL SSMS 2014

Any help would be appreciated!! This is my first post so sorry for odd formatting.

Does the verbose parameter do what you need?

Martin Smith
– Martin Smith

2017-02-03 19:35:04 +00:00
Commented Feb 3, 2017 at 19:35 — Martin Smith
– Martin Smith, Commented Feb 3, 2017 at 19:35

jbsmith · Accepted Answer · 2017-02-03 20:38:15Z

2

It looks like the following link explains exactly this problem:

https://sqlnotesfromtheunderground.wordpress.com/2015/09/09/powershell-outputting-a-sql-server-query-result-from-the-message-tab/

Essentially, what you are seeing in the 'Messages' tab are not results from the query, but rather just PRINT statements (essentially the same as Write-Host or Console.WriteLine). If you use Invoke-SqlCommand2, its -Verbose option will capture these PRINT statements to the Verbose PowerShell stream. To then write this stream to a text file, you have to specify the specific stream (in this case, 4):

Invoke-Sqlcmd2 -ServerInstance . -Database master -Query "PRINT 'Export This result'" -Verbose 4> Out-File C:\Temp\Test.txt

answered Feb 3, 2017 at 20:38

jbsmith

1,66613 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

AJPG Over a year ago

Having some real beginner moments here. When trying to run regular {Invoke-Sqlcmd -ServerInstance $ProdServer -Database $Database -Query "PRINT 'Export This result'" -Verbose 4> Out-File "C:\users\admin-ag\desktop\Test.txt"} it gives the error "out-file : Cannot open file because the current provider (Microsoft.SqlServer.Management.PSProvider\SqlServer) cannot open a file."

AJPG Over a year ago

Also don't know how to use Invoke-SQLCMD2 , it displays this error: "Invoke-Sqlcmd2 : The term 'Invoke-Sqlcmd2' is not recognized as the name of a cmdlet, function, script file, or operable program."

jbsmith Over a year ago

Ah -- I don't have the SQLPS module available to me, so I wasn't sure where the Invoke-SqlCmd2 cmdlet came from. It looks like it's a community function from GitHub: github.com/sqlcollaborative/Invoke-SqlCmd2 I'm not sure why you're getting the Provider error; I'd try a "cd c:\" before running your command to make sure you're not in a SQL provider context.

AJPG Over a year ago

OK I think I got it working, I'll update next week with results!

AJPG Over a year ago

So my line is producing a .txt file but still has no data from the messages in it. If I run your line with Out-File it fails because the "4>" does not need Out-File. When I run your line without the Out-File and just the 4> it works and has a text file with the "Export This result" in it. Unfortunately, running a normal query with a PRINT seems to work but does not work when running my input file (.sql) and no PRINT statement.

|

Mehdi L · Accepted Answer · 2019-08-06 15:26:27Z

2

I had the same issue but instead in powershell script i use it in a command and i used -verbose. like this

Invoke-Sqlcmd -ServerInstance '.\Your_server_instance' -Database 'DATABASE_Name' -InputFile "D:\Your_script.sql" verbose 4> "C:\sql\YOUR_OUTPUT_FILE.txt"

so i think this code should work for you

{    
Import-Module SQLPS         
$Data = Invoke-Sqlcmd -InputFile $SQLQuery -ServerInstance $Server -Database $Database -QueryTimeout 0    
$Data -verbose *>  "$Output$Database$date.txt"    
}

for -verbose *> it streams All output you can redirect specific streams :

1 Success output
2 Errors
3 Warning messages
4 Verbose output
5 Debug messages

answered Aug 6, 2019 at 15:26

Mehdi L

212 bronze badges

Collectives™ on Stack Overflow

Powershell - Output SQL Messages to file

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related