2

I'm running a set of sql queries and they are not reporting the row affect until all the queries have ran. Is there anyway i can get incremental feedback.

Example:

   DECLARE @HowManyLastTime int

SET @HowManyLastTime = 1

WHILE @HowManyLastTime <> 2400000
 BEGIN

      SET @HowManyLastTime = @HowManyLastTime +1
    print(@HowManyLastTime)
 END

This doesn't show the count till the loop has finished. How do i make it show the count as it runs?

Dan
  • 29,100
  • 43
  • 148
  • 207
  • Your query shows the count as it runs in the messages tab when I run it on sql2008. – Jimbo Jun 08 '11 at 13:00
  • Are/Can you put `GO` between your statements? If you put a GO in though, your variables will fall out of scope so be careful. – Duncan Howe Jun 08 '11 at 13:56

2 Answers2

2

To flush recordcounts and other data to the client, you'll want to use RaisError with NOWAIT. Related questions and links:

In SSMS this will work as expected. With other clients, you might not get a response from the client until the query execution is complete.

Community
  • 1
  • 1
Tao
  • 13,457
  • 7
  • 65
  • 76
  • I tried `RAISERROR ('Your message', 0, 1) WITH NOWAIT` but that still has the same behviour – Dan Jun 08 '11 at 12:19
  • What client are you using? Is this from your application (eg ASP.Net), or SSMS, or some other environment? – Tao Jun 08 '11 at 12:58
  • 1
    If you're using SSMS, is it possible that your UI is set to "Results to Grid" and you're just not switching to the "Messages" tab in the UI? (if there are no resultsets, SSMS switches to the "Messages" tab automatically when the query completes, but not before) – Tao Jun 08 '11 at 13:01
0

SQL tends to be 'set-based', and you are thinking procedurally and trying to make it act systematically. It really doesn't make sense to do this in SQL.

I would be asking you motivation for doing this, and is there anything better that can be tried.

James Wiseman
  • 29,946
  • 17
  • 95
  • 158