[Fix] - flush the PRINT buffer in TSQL

Wikitechy | 4991 Views | sql server | 08 Jun 2016

 

Scenario :

  • T-SQL scripts and stored procedures ignore the PRINT statements and use the RAISERROR to show progress output after finishing the executions completely.

PRINT :

  • Disadvantages of PRINT is output buffering. Consider the below code:
DECLARE @a INT;
SET @a = 1;
WHILE(@a < 100)
BEGIN
   PRINT @a;
   SET @a = @a + 1;
   WAITFOR DELAY '00:00:00.2'
END


  • The above loop code prints an increasing value for the variable a, waiting for 0.2 seconds after each print message. Up to 40 iterations is completed before generating the output. Hence the PRINT lags.

RAISERROR :

  • Invoke RAISERROR with a severity between 0 and 9 to just print messages. The NOWAIT option informs SQL Server to send the output immediately thus avoiding the problems that PRINT has.
DECLARE @a INT;
SET @a = 1;
WHILE(@a < 10)
BEGIN
   RAISERROR('wikitechy test',0,1) WITH NOWAIT;
   SET @a = @a + 1;
   WAITFOR DELAY '00:00:01'
END

  • When this code is executed the code will have print behavior without print lagging nature.

Fix 1 :

1.Implement the RAISERROR function instead of PRINT as follows:

        RAISERROR( 'This message will show up right away...',0,1) WITH NOWAIT

2.Do not replace PRINTS with RAISERROR. Use it once or twice in a loop with first parameter NVARCHAR variable as follows:

DECLARE @wikiTechyMsg NVARCHAR(100)
SELECT @ wikiTechyMsg = ‘Enter the message here'
RAISERROR (@wikiTechyMsg, 0, 1) WITH NOWAIT

Fix 2 :

  • When executing in scripts in batch use the GO command along with PRINT as, 

PRINT ‘wikitechy'

GO

Applies to sqlserver version : 

  • SQL Server 1.1 (16 bit)
  • SQL Server 4.2A (16 bit)
  • SQL Server 4.2B (16 bit)
  • SQL Server 4.21a
  • SQL Server 6.0
  • SQL Server 6.5
  • SQL Server 7.0
  • SQL Server 7.0 OLAP Tools
  • SQL Server 2000
  • SQL Server 2000 64-bit Edition
  • SQL Server 2005
  • SQL Server 2008
  • Azure SQL DB
  • SQL Server 2008 R2
  • SQL Server 2012
  • SQL Server 2014
  • SQL Server 2016

Related Error Tags :

  • Flush print statements to client 
  • sql server - Flush SQL Management Studio Message Buffer - Stack
  • SQL Server: PRINT output doesn't appear immediately
  • sql server - Print message comes out in batches 
  • Using raiseerror to flush print-buffer
  • PRINT/SELECT Statement messages within WHILE 
  • T-SQL PRINT flush?
  • PRINT vs. RAISERROR
  • Using the NOWAIT option with the SQL Server RAISERROR statement
  • sql server - How do I flush the PRINT buffer in TSQL?
  • T-SQL PRINT flush?



Workshop

Bug Bounty
IOT Hackathon
Webinar

Join our Community

Advertise