A test SQL script (SqlScript.sql) like this tests three ways of output. That is standard output by SELECT, event output by PRINT and error output by RAISERROR.
SET NOCOUNT ON;
SELECT @@VERSION AS [sql_version];
GO
PRINT N':: Hello world from PRINT';
GO
SELECT ':: Hello world from SELECT' AS [hello];
GO
RAISERROR( N':: Hello world from RAISERROR', 18, 0 ) WITH NOWAIT;
GO
SELECT ':: End of script.' AS [script_end];
The calling PowerShell script file is
& sqlcmd.exe -S "Titanium" -E -r1 -i "SqlScript.sql" > SqlScript.log 2>&1
After execution by SQL Server PowerShell (sqlps.exe) the log file is
sql_version
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)
Mar 29 2009 10:11:52
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1
SQLCMD.EXE : :: Hello world from PRINT
At H:\Script Library\sqlps\Invoke-SqlCmd.ps1:14 char:2
+ & <<<< sqlcmd.exe -S "Titanium" -E -r1 -i "SqlScript_1.sql" > SqlScript_1.log 2>&1
+ CategoryInfo : NotSpecified: (:: Hello world from PRINT:String) [], RemoteException
+ FullyQualifiedErrorId : NativeCommandError
hello
--------------------------
:: Hello world from SELECT
Msg 50000, Level 18, State 1, Server TITANIUM, Line 2
:: Hello world from RAISERROR
script_end
-----------------
:: End of script.
As the PRINT statement is a event, the placement of the PRINT output can vary.
Okay, I kind of cheated by still using sqlcmd.exe.
But this way I get the wanted output in the log file and in the right order. Also I get the features of PowerShell that I find much more powerfull than sqlcmd.exe or Windows Shell (cmd.exe).