HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlModerate

Keep track of time intensive stored procedure with prints

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
storedintensivetrackwithprintskeeptimeprocedure

Problem

I am no database admin nor is my SQL-foo any good (yet), but have a few tasks to get done in MSSQL, and one is a rather time intensive procedure where I fetch from a CURSOR and WHILE @@FETCH_STATUS = 0, I have multiple SELECTs and an UPDATE on giant tables. (The procedure works, I've checked with a colleague)

Now in a script or a program I'd have a simple print @current_cursor or print a counter in every WHILE just to have some feedback.

How could I do something like this in MSSQL? (Working on Microsoft SQL Server Enterprise Edition v9.00.3042.00, i.e. SQL Server 2005 Service Pack 2)

Could I do a print @current_cursor, or would that be a no-go in my case?

edit

I've tested the RAISEERROR with the following statements:

DECLARE @cur_id int; 
SET @cur_id = 0;

DECLARE Cur CURSOR FOR
    SELECT ID FROM TableA
    WHERE ID > 100  ORDER BY ID DESC

OPEN Cur
FETCH NEXT FROM Cur INTO @cur_id;

WHILE @@FETCH_STATUS = 0
BEGIN
    RAISERROR(@cur_id, 0, 0) WITH NOWAIT
    FETCH NEXT FROM Cur INTO @cur_id;
END


But this leads to some time of waiting without output and then an endless wall of (I assume one for every RAISERROR):

Msg 18054, Level 16, State 1, Line 17
Error 983700, severity 0, state 0 was raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage.

Solution

In SQL Server (and I think this should work in the ancient version you say you're using), the normal method for this is to use RAISERROR. PRINT won't display any output until the process finishes which is not very useful. I use this technique a lot in batch scripts for say updates and deletes where I want to keep the batch size small.

In the example below, I set a @Batchsize variable and my loop just works on that many rows at a time, then I output that with a RAISERROR. It's important to use a severity of 0, which means nothing will treat it as an error, just as an informational message.

DECLARE @MsgStr varchar(200) = 'Inserted ' + CAST(@BatchSize AS varchar(10)) + ' rows...'

RAISERROR(@MsgStr, 0, 0) WITH NOWAIT

Code Snippets

DECLARE @MsgStr varchar(200) = 'Inserted ' + CAST(@BatchSize AS varchar(10)) + ' rows...'

RAISERROR(@MsgStr, 0, 0) WITH NOWAIT

Context

StackExchange Database Administrators Q#82573, answer score: 10

Revisions (0)

No revisions yet.