T-sql – Messages in SQLCMD script output are not in order

sqlcmdt-sql

I am running a batch file that launches a SQLCMD script that creates an Agent job from TFS Release Manager.

The script assigns SQLCMD parameters to variables, lists the variables using selects and displays some feedback in the running of the script throughout the script.

It looks something like this:

SET NOCOUNT ON

DECLARE 
    @Var1 NVARCHAR(100) = '$(Var1)'
    ,@Var2 NVARCHAR(100) = '$(Var2)'

SELECT '@Var1' AS VarName,@Var1 AS VarValue
UNION ALL
SELECT '@Var2',@Var2

PRINT 'Do the first thing'
--delete sql agent job

PRINT 'Do the next thing'
--create sql agent job

PRINT 'Do the last thing'
--add steps

Every so often, in the output, I see the PRINT messages in the middle of the SELECT output.

Is there a way to guarantee the order of these messages?

Best Answer

PRINT output can be buffered. A great way around this is to use RAISERROR (@msg, 0, 1) WITH NOWAIT; instead.

For example:

DECLARE @msg nvarchar(1000);

SET @msg = 'Do the first thing';
RAISERROR (@msg, 0, 1) WITH NOWAIT;

SET @msg = 'Do the second thing';
RAISERROR (@msg, 0, 1) WITH NOWAIT;

SET @msg = 'Do the third thing';
RAISERROR (@msg, 0, 1) WITH NOWAIT;
Related Question