Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    268

    Unanswered: T-SQL PRINT flush?

    I have a long running (48 hour+) stored proc. I've added PRINT statements that print "1%", "2%", etc to provide progress so I can get a ballpark idea of how far along the process is. I ran the stored proc and realized that this won't work. I don't see the output of the print statements until the stored proc is completed. I'm running this from Query Analyzer (SQL Server 2000 SP3a).

    For example, the following will wait ten seconds and then print both statements; it doesn't print one then wait, then print the other. Is there any flush command to make the print statements take effect immediately?

    PRINT 'before'
    WAITFOR DELAY '000:00:10'
    PRINT 'after'

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    No. You can use RAISERROR instead though. Those are shown immediately.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Oct 2003
    Posts
    268
    Quote Originally Posted by derrickleggett
    No. You can use RAISERROR instead though. Those are shown immediately.
    Thanks for the tip. I tried the following and it didn't work. I switched my code to log to a status table which is good enough. Thanks!

    Code:
    PRINT 'before'
    RAISERROR ('test', 16, 1)
    WAITFOR DELAY '000:00:10'
    PRINT 'after'

  4. #4
    Join Date
    Jul 2004
    Posts
    52
    I believe back in SQL 6.5 I remember reading about a 255 or 2048 byte buffer for print statements. You might try printing a bunch of spaces to flush the buffer more frequently.

  5. #5
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    If your example doesn't work, you aren't testing what you just typed. I just tried your example and it worked.

    I also tried this:

    CREATE PROCEDURE djl_test AS

    DECLARE @COUNTER INT
    SELECT @COUNTER = 1

    BEGINNER:
    BEGIN TRANSACTION
    PRINT 'before'
    RAISERROR ('test', 16, 1) WITH NOWAIT
    WAITFOR DELAY '000:00:10'
    PRINT 'after'
    SELECT @COUNTER = @COUNTER + 1
    IF @COUNTER <= 10
    GOTO BEGINNER
    PRINT 'test print'
    RAISERROR ('test raiserror', 16, 1) WITH NOWAIT
    COMMIT TRANSACTION

    It prints before and test before it prints after every time.
    Last edited by derrickleggett; 08-22-04 at 23:50.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  6. #6
    Join Date
    Mar 2011
    Posts
    1
    RAISERROR method doesn't work for me all get printed at the very end or after the wait. There must be a global server or database property?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •