SQL Server: how to print during program execution?

Posted on 2014-08-28
Last Modified: 2014-09-03
Print output appears in Messages window only after all sprocs execution completes. How to see results during execution, not in the end.
Question by:quasar_ee
    LVL 3

    Expert Comment

    Not sure I understand what you mean but I usually use GO after each proc block to execute a batch of script at a time
    LVL 15

    Expert Comment

    You can use the "print" keyword like :

    declare @min int, @max int , @cmd varchar(20)
    set @min =1
    set @max=10000000
    set @cmd='Current Value is: '
    while @min <=@max
    print @cmd+cast(@min as varchar)

    set @min=@min+1
    LVL 8

    Expert Comment

    you add print statements whenever required
    LVL 65

    Accepted Solution

    >How to see results during execution, not in the end.
    You'll need to provide us some more details.

    I've seen some VERY long SP's where in order to be able to view progress the developers created a cursor where rows were processed 50,000 at a time, and the end of each cursor loop logged a row to a 'logging' table to show the status.  That way, developers can query the table to see the progress of the SP.
    LVL 68

    Assisted Solution


    You're quite right: SSMS buffers PRINT results, so you won't see them immediately; when the buffer fills, it flushes and then  you see the results.  I don't know of a really good way around that, although I've tried to find one.

    You could try "RAISERROR('...', 1, 1) WITH NOWAIT", but I've seen that get buffered also.

    You could also try PRINTing long dummy strings to fill the buffer, but then you have to read around those when you look at the output.
    LVL 75

    Expert Comment

    by:Anthony Perkins
    Good question.  If it is that important to you, your best bet is to use Jim's solution of inserting into a table and polling it frequently.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    Title # Comments Views Activity
    SQL Filter Question 8 56
    SQL Schedule Job 1 12
    SQL Searching 3 35
    How to disable/enable multiple sql jobs in efficient way 11 73
    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now