[Fix] - flush the PRINT buffer in TSQL
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 7.0 OLAP Tools
- SQL Server 2000 64-bit Edition
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
- Using the NOWAIT option with the SQL Server RAISERROR statement
- sql server - How do I flush the PRINT buffer in TSQL?
