patternsqlModerate
Keep track of time intensive stored procedure with prints
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
Now in a script or a program I'd have a simple print @current_cursor or print a counter in every
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
edit
I've tested the
But this leads to some time of waiting without output and then an endless wall of (I assume one for every
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;
ENDBut 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
In the example below, I set a
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 NOWAITCode Snippets
DECLARE @MsgStr varchar(200) = 'Inserted ' + CAST(@BatchSize AS varchar(10)) + ' rows...'
RAISERROR(@MsgStr, 0, 0) WITH NOWAITContext
StackExchange Database Administrators Q#82573, answer score: 10
Revisions (0)
No revisions yet.