2010-03-20

SQL script file output using SQLPS

To automate tasks I often need to execute one or more script files (*.sql). Usually I have used sqlcmd.exe, but I would like to convert to sqlps, as it seems to be the future of SQL Server automation according to Microsoft. That is at least my personal guess...

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 (Build 7600: )
                                                                                                             
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).