Post reply

TSQL Progress printouts in a loop

  • April 28, 2009 at 11:28 am

    #202638

    Hi,

    why do the print commads and 'Rows affected' printouts appear only after the loop has finished? Is there a way to accomplish the progress prints like desired below? Sql Server 2008

    declare @STARTweek tinyint, @ENDweek tinyint, @sqlstring nvarchar(200), @Year smallint

    set @Year = 2004

    WHILE @Year <= 2004
    BEGIN
    Set @STARTweek = 1
    Set @ENDweek = 5
    WHILE @ENDweek <= 10
    BEGIN

    Set @sqlstring =N'
    update STG
    Set TuoteKEY = D.TuoteKEY
    From dbo.STG_KIINTIO_VK_vDW STG JOIN DW.dbo.D_TUOTE D ON (STG.TuoteKoodi=D.TuoteKoodi)
    Where VUOSI = ' + CAST(@Year as varchar(4)) + N' and VIIKKO >= ' + CAST(@STARTweek as varchar(2)) + N' and VIIKKO <= ' + CAST(@ENDweek as varchar(2)) [b]Print @sqlstring [/b] execute (@sqlstring)
    --execute sp_executesql @sqlstring

    [b]print ''
    print 'SHRINKFILE(N''STG_VanhaDW_log'')'
    Print ''[/b]

    DBCC SHRINKFILE(N'STG_VanhaDW_log')

    Set @STARTweek += 5
    Set @ENDweek += 5

    END
    Set @Year += 1
    END

    Ville

  • April 28, 2009 at 11:39 am

    #985342

    instead of printing, you have to use RAISERROR instead to get your ongoing progress:

    --print error immediatly in batch

    while 0=0

    begin

    raiserror ('my Progress So far....',0,1) with nowait

    waitfor delay '00:00:05'

    end

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • April 28, 2009 at 12:04 pm

    #985362

    jep, works fine, thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply