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
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>
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