Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
|
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 11th, 2008, 05:22 AM
Authorized User
 
Join Date: Jun 2004
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default Loop a select but with a delay between each try

This is an odd request I know but we are doing some testing on some processes and need to automate a simple set of select statements purly to hog some CPU and to see if any locks occur.

We have the following bit of example code

set nocount on
declare
@count int
begin
set @count = 1
 while (@count <= 10)
    waitfor delay '00:0:03';
      select count(*) from TESTDATABASE.webtaskdetails;
      select @count = count(*) from TESTDATABASE.diary where casenam = 'VARIABLE' and status = 'P'
set @count = @count + 1
END

If we remove the "waitfor" line the code runs as required but with no 3 second delays between selects. With the "waitfor" included it seems to hang altogether. How can we incorporate the delay between selects?

 
Old June 11th, 2008, 06:26 AM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

It doesn't wait forever... it runs. The problem is that it won't produce an output until the entire loop is done processing. Here's an example of how to overcome that printing problem... the details are in the comments... read 'em... :D

Code:
--===== How to force messages to screen on long running queries

--=========================================================================
--        What normally happens is there is no display until the end of run
--=========================================================================
    PRINT 1

  WAITFOR DELAY '00:00:5'
    PRINT 1.5
  WAITFOR DELAY '00:00:5'
    PRINT 2

  WAITFOR DELAY '00:00:5'
    PRINT 3
GO
--=========================================================================
--        Raiserror forces the messages to be flushed as the run proceeds
--=========================================================================
    PRINT 1
--======= Flush message 1 to client
RAISERROR ('After 1',10,1) WITH NOWAIT

  WAITFOR DELAY '00:00:5'
    PRINT 1.5
  WAITFOR DELAY '00:00:5'
    PRINT 2
--======= Flush waiting message(s) to client
       -- This will send 1.5 and 2 to the client
RAISERROR ('After 2',10,1) WITH NOWAIT

  WAITFOR DELAY '00:00:5'
    PRINT 3
--===== Flush message 3 to client
RAISERROR ('After 3',10,1) WITH NOWAIT
--Jeff Moden





Similar Threads
Thread Thread Starter Forum Replies Last Post
How to loop for Select Values rohit_ghosh Excel VBA 3 May 8th, 2007 03:29 PM
Select box for loop(probably really simple) interrupt Javascript How-To 2 March 18th, 2005 12:25 PM
how to delay and loop until file arrives brettbrimhall SQL Server DTS 5 December 13th, 2004 11:11 AM
Nested Loop Select Box Issue mat41 Classic ASP Basics 5 August 24th, 2004 11:02 PM
time delay pab006 Classic ASP Basics 2 January 29th, 2004 05:16 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.