How To Print A results During A long Batchs
-
I want To print results to the screen using the print
while runing stored procedures In a havy batch to track the progress of the batch
I have som e nested store procedures that runs on table s columns indexes
I want to be able to track the progress
right now the printing ocours when the sp finishes or the batch is finished
-
This might help
http://www.umachandar.com/technical/SQL6x70Scripts/Main70.htm
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Gil, if you more timely output in the results pain of QA, instead of using PRINT,
use
raiserror ('[Your text here]',0,1) With Nowait
or
raiserror (@YourCharVariableHere,0,1) With Nowait
Once you understand the BITs, all the pieces come together
-
When using print be aware that the print statement degrades in performance as the file holding the print is growing. In you shop we had a batch job using print heavily. The execution time was cut down with a factor 50 (fifty!) by omitting the print statements.
-
I use a method similar to the one scripted on the page Frank linked, except I don't use permanent temp tables, instead favoring a single message table that contains a field to identify the script/process/whatever that put the message there. Having things running between 'begin tran/commit tran' can really mess with it though. For those, I think the raiserror is probably better.
Alternately, to get around the explicit tran issue, I think you can add the messages via calls to OPENQUERY since a server is linked to itself by default.
Matthew Galbraith
-
Hi,
A simpler way too would be to create a table and script the start of a procedure and the end of the procedure , this will notify you how far you SP is and how long the different steps\queries within your SP took.
try this in your SP before each query
First create a table eg. Status
INSERT Status
(DateCaptured, Comment)
VALUES (GETDATE(), 'BEGIN INSERT --SP\query--')
On completion of the SP or nested queries
INSERT Status (DateCaptured, Comment)
VALUES (GETDATE(), 'END INSERT -- SP\query--')
Also script (truncate) the table you created in your SP before you begin the SP\query.
Roy
-
That's pretty much what I was talking about... I was forgetting, however, that you can get around the message/status table being locked inside long-running transactions by using:
SELECT * from message WITH(NOLOCK)
That will let you look at the table while your transaction is running, despite the lock on the table due to your script inserting into it during an explicit transaction. The other plus is that if you stick a datestamp field in the table, you can do some very simple performance monitoring of the query and determine where it runs slowly. I have used this exact tactic in the past, it’s just been a little while since I needed it.
The one area that would still benefit from RAISERROR or the OPENQUERY method is that you could rollback the transaction you're watching, without blowing away the performance data. Doesn't matter if you don't care how long each part took, just so you can tell where it is while running, but if you're trying to tune the script in addition, then keeping the data would be nice, even if the transaction has to be rolled back.
Matthew Galbraith
-
I would run the lon running process as a JOB then when call returns from start_job the query to that progress table can be done periodically.
* Noel
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply