CODEWITHSUNDEEP
sqlpowershellinvoke
Magnhild Hagen
Magnhild Hagen

Reputation: 81

How to route SQL print output to log file?

Anyone who knows how to route print ' ' in an sql script to a logfile when using Invoke-Sqlcmd?

I tried using sqlcmd -o someoutfile.txt, but it overwrites, it does not append to existing file. And if an SQL error occurs, only the error message is sent to file, not the print ' '. When using Invoke-Sqlcmd | out-file someoutfile.txt -Append, it appends only Write-Output and eventually SQL errors, but not the print ' ' in the sql script excuted.

Has anyone found a solution for this?

Upvotes: 2

Views: 7508

Answers (2)

Alexander Shapkin
Alexander Shapkin

Reputation: 1262

And one moment...

The command "Invoke-Sqlcmd" has a parameter -SeverityLevel. SeverityLevel specifies the lower limit for the error message severity level Invoke-Sqlcmd returns to the ERRORLEVEL PowerShell.

Invoke-Sqlcmd does not report severities for informational messages that have a severity of 10!

Severity Level 10: Status Information This is an informational message that indicates a problem caused by mistakes in the information the user has entered. Severity level 0 is not visible in SQL Server.

Upvotes: 0

Chad Miller
Chad Miller

Reputation: 41767

Invoke-SqlCmd implements T-SQL PRINT statements and RAISERROR using the verbose parameter. To capture verbose output, first you'll need to include the parameter in your call to invoke-sqlcmd i.e. invoke-sqlcmd -verbose and next you can do one of two things:

If you're using Powershell V3 or higher you can redirect verbose output:

invoke-sqlcmd -verbose 4>&1 | outfile someoutfile.txt

If you're using Powershell V2 you can't redirect verbose output to a file, however you can use start-transcript to send all screen output to a file. One gotcha with this approach--it will not work with SQL Agent Powershell job step. It will however work with a cmdexec job step which calls powershell.exe.

Upvotes: 4

Related Questions