SQL Server: How to notify client about progress made using frequent PRINT statements.
When you are executing long running queries or executing a long batch, sometime, you want to send some quick responses back to the client (i.e. Management Studio [SSMS], etc.) to show the progress.
Usually I add PRINT statements at every block of statements to show the progress made from one block of statements to the next block of statements. This gives me confirmation about the progress as it is happening.
At the end of running the batch Sql Server will send every response to the client, but if you want to do this at intermittent steps use RAISERROR WITH NOWAIT to flush everything to the client.
Example:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
| PRINT '-------------------------------------------------------------'PRINT 'Starting Batch: ' + CONVERT(VARCHAR, GETDATE(), 109)RAISERROR ('Start of Step 1', 10, 1) WITH NOWAIT -- -- Step 1 Sql statements -- WAITFOR DELAY '00:00:10' -- -- More Step 1 Sql statements --RAISERROR ('End of Step 1', 10, 1) WITH NOWAIT -- -- Step 2 Sql statements -- WAITFOR DELAY '00:00:10' -- -- More Step 2 Sql statements --RAISERROR ('End of Step 2', 10, 1) WITH NOWAITPRINT 'End of Batch: ' + CONVERT(VARCHAR, GETDATE(), 109)PRINT '-------------------------------------------------------------' |
RAISERROR with NOWAIT allows to send messages immediately to client. Refer to MSDN article here