Friday 23 October 2015

PowerShell Invoke-sqlcmd Verbrose output to a File

Recently I have had lot queries regarding how to redirect the SQL output message from invoke-sqlcmd/invoke-sqlcmd2 function into a notepad.When you use Out-File it will return only the result set but it will not write messages associated as you expect in SSMS (Errors are not written nor any PRINT Statement when out-file is used). 

When you use -Verbose with the invoke-sqlcmd or invoke-sqlcmd2 it will write all the associated messages to your screen and All we need to do is is redirect the Verbose output to a File, how do we do it standard >> will not redirect Verbose to a File. Then I happended to Stumble across the MSDN link  https://technet.microsoft.com/en-us/library/hh847746.aspx which gave a detailed explanation of all the Redirectors :-) 

So all I need to use is 4> or 4 >> instead >> to accomplish this, , Try to execute the below 6 examples and see how the output is redirected, Only in Example6 You would be able to get a result equivalent to SSMS result editor.

Example1:-(Expected Result Powershell will not print 'HELLO WORLD' on the result editor/Stdout)
invoke-sqlcmd -Query "PRINT 'Hello World'" -ServerInstance "SQLTestServer\INSTANCE" -Database TestDB

Example2:-(Expected Result Powershell will not print 'HELLO WORLD'  to the test file)
invoke-sqlcmd2 -Query "PRINT 'Hello World'" -ServerInstance "SQLTestServer\INSTANCE" -Database TestDB|out-File <TestPath>

Example3:-(Expected Result Powershell will print  'HELLO WORLD' on result editor/Stdout)
invoke-sqlcmd -Query "PRINT 'Hello World'" -ServerInstance "SQLTestServer\INSTANCE" -Database TestDB -Verbose

Example4:-(Expected Result Powershell will print  'HELLO WORLD' on result editor/Stdout but The out File will be empty)
invoke-sqlcmd -Query "PRINT 'Hello World'" -ServerInstance "SQLTestServer\INSTANCE" -Database TestDB -Verbose|out-File <TestPath>

Example5:-(Expected Result Powershell will print  'HELLO WORLD' on result editor/Stdout but The out File will be empty)
invoke-sqlcmd -Query "PRINT 'Hello World'" -ServerInstance "SQLTestServer\INSTANCE" -Database TestDB -Verbose> .\Test.out

Example6:-(Expected Result Powershell will print 'HELLO WORLD'  to the test file)
invoke-sqlcmd -Query "PRINT 'Hello World'" -ServerInstance "SQLTestServer\INSTANCE" -Database TestDB -Verbose 4> .\Test.out