none
PRINT within WHILE loop is not being displayed... RRS feed

  • Question

  • Suppose I have a SQL block like the following. I added a PRINT so that I know that the process is running correctly and it's not in an infinite loop.

    Unfortunately, when I execute the script (and this WHILE loop), I notice that the PRINT is not displayed until the script runs completely or the script is stopped (and rollback is complete). But whils it's running, I see nothing on the screen.

    Since I'm transferring 4M+ records, I need a way to know that it's running correctly.

    Any help is appreciated. Thanks.

    While (condition)
    BEGIN
        BEGIN TRANSACTION [TRANS1]
            INSERT INTO TABLE
            select * from TABLE2
            where
                myDate > @date1 and
                myDate < @Date2
        COMMIT TRAN [TRANS1];
        PRINT 'INSERTED RECORDS FROM ' + @date + ' ' + @date2
    END
    Friday, November 25, 2011 9:30 PM

Answers

  • The reason this happens is because SQL Server buffers the output.

    You can do this instead:

    SELECT @msg = 'INSERTED RECORDS FROM ' + @date + ' ' + @date2
    RAISERROR(@msg, 0, 1) WITH NOWAIT

    The NOWAIT option instructs SQL Server to send the output immediately.

    Unfortunately, this only works for the first 500 messages. After this SQL Server Mgmt Studio starts buffering the messages 50 at a time.

    If you happen to have a machine with the old SQL 2000 tools, they come in handy here, as Query Analyzer does not buffer at all.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by skc_chat Thursday, December 1, 2011 8:35 AM
    • Marked as answer by Kalman TothModerator Tuesday, December 6, 2011 3:42 PM
    Friday, November 25, 2011 9:48 PM
  • Take a look at this article

    http://www.mssqltips.com/sqlservertip/1660/using-the-nowait-option-with-the-sql-server-raiserror-statement/


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Friday, November 25, 2011 9:40 PM
    Moderator
  • I doubt that you can achieve desired and professional results with PRINT.

    Look, Kalman, it all depends on what you want to do. I've run jobs that ran for days where we made extensive use of RAISERROR WITH NOWAIT. Don't worry we had log tables as well. But the RAISERROR messages served as a quick update how far the job had progressed.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, December 1, 2011 11:23 PM

All replies

  • Take a look at this article

    http://www.mssqltips.com/sqlservertip/1660/using-the-nowait-option-with-the-sql-server-raiserror-statement/


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Friday, November 25, 2011 9:40 PM
    Moderator
  • The reason this happens is because SQL Server buffers the output.

    You can do this instead:

    SELECT @msg = 'INSERTED RECORDS FROM ' + @date + ' ' + @date2
    RAISERROR(@msg, 0, 1) WITH NOWAIT

    The NOWAIT option instructs SQL Server to send the output immediately.

    Unfortunately, this only works for the first 500 messages. After this SQL Server Mgmt Studio starts buffering the messages 50 at a time.

    If you happen to have a machine with the old SQL 2000 tools, they come in handy here, as Query Analyzer does not buffer at all.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by skc_chat Thursday, December 1, 2011 8:35 AM
    • Marked as answer by Kalman TothModerator Tuesday, December 6, 2011 3:42 PM
    Friday, November 25, 2011 9:48 PM
  • Instead of PRINT, you can log the message to a table.  You can then just look in the table for progress.

    CREATE TABLE GeneralLog (
    	LogID INT IDENTITY(1,1) PRIMARY KEY,
    	Msg nvarchar(512),
    	LogDate datetime default (CURRENT_TIMESTAMP));
    GO
    
    -- Loop insert
    INSERT GeneralLog (Msg)
    SELECT  'ALPHA APP: INSERTED RECORDS FROM ' + @date + ' ' + @date2
    

     


    Kalman Toth, SQL Server & Business Intelligence Training; Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
    Thursday, December 1, 2011 6:43 AM
    Moderator
  • use as suggested by Erland.

     

    RAISERROR (.............) WITH NOWAIT;

    Thursday, December 1, 2011 8:37 AM
  • I doubt that you can achieve desired and professional results with PRINT.

    Look at my logging solution, it is really reliable and useful.

    To get visual display of action, assume this is development/testing, you can use SQL Server Profiler. You can just filter textdata, in addition to event filtering, for what you are interested in like "insert", "table1", "select", "table2".  Note this is not for production.

    SQL Server Profiler tutorial video and article:

    http://sqlserverpedia.com/wiki/Using_SQL_Server_Profiler

    Related article: http://www.sqlusa.com/bestpractices2005/performancetuning/sqlserverprofiler/

     

     


    Kalman Toth, SQL Server & Business Intelligence Training; Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
    Thursday, December 1, 2011 4:48 PM
    Moderator
  • I doubt that you can achieve desired and professional results with PRINT.

    Look, Kalman, it all depends on what you want to do. I've run jobs that ran for days where we made extensive use of RAISERROR WITH NOWAIT. Don't worry we had log tables as well. But the RAISERROR messages served as a quick update how far the job had progressed.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, December 1, 2011 11:23 PM
  • Perhaps the RAISEERROR way to get information to the console worked for older versions of SQL Server, but I found it doesn't work..  Just try the following for yourself:

    RAISERROR('Did you have to wait 5 seconds to read this?', 0, 1) WITH NOWAIT;
    WAITFOR DELAY '00:00:05';

    Another post in this reply talks about using another table to write to.   If you plan on using this table for other things, writing to another table is ok.  But if like most, you're just trying to get a status on where a time-intensive process is at, so leaving tables behind would be frowned upon.  Instead you can use a global temp table.  Then just write to that table, and you can query that table from another session window.  When you close the session window that created the global temp table, its deleted automatically.  No mess left behind.

    CREATE TABLE ##log
    (
        -- fields you need...
    )

    WHILE
    BEGIN
        -- Do stuff ... 
        INSERT INTO ##log ...
    END

    Monday, January 6, 2014 6:35 PM
  • Worked for me in SQL Server 2012 SP1. I found that message right away.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, January 6, 2014 6:46 PM
    Moderator
  • mns_mwgbi

    I found the same in SSMS 2008 R2 if the output is set to grid, as my query did not have any results. Therefore you don't get output displayed until the end. However, if you change the output to Text, or use the Window->Next Pane (F6) menu, you should see the output as it is created.

    Monday, March 9, 2015 8:08 PM
  • RAISERROR('Did you have to wait 5 seconds to read this?', 0, 1) WITH NOWAIT;
    WAITFOR DELAY '00:00:05';

    You terminated the raiseerror line so that executed before the 5 sec. If you take the semicolon out 5 sec will elapse before the raiseerror statement.
    Wednesday, August 31, 2016 12:30 AM
  • RAISERROR('Did you have to wait 5 seconds to read this?', 0, 1) WITH NOWAIT;
    WAITFOR DELAY '00:00:05';

    You terminated the raiseerror line so that executed before the 5 sec. If you take the semicolon out 5 sec will elapse before the raiseerror statement.

    No, this is completely wrong. The semicolon has no function at all. It is just an optional statement terminator.

    Wednesday, August 31, 2016 7:20 AM
  • Hi All,

          We are executing script using sqlcmd with output file. but output file can not get script progress. will display output after finish execution.and  any method to use this work.

    Note : if we execute SQL script SSMS manually  then we can get  script execution progress.(inside the sql script used "RAISERROR WITH NOWAIT")


    Thanks & regards,
    Tharindu Dhaneenja (www.databaseusergroup.com)

    Thursday, November 3, 2016 9:07 AM