Using waitforto set delay

Question

Using waitforto set delay

Deviprasad

Ten Centuries

Points: 1060
More actions
July 22, 2008 at 12:58 pm

#126470

Hi,
My requirement is to compare count of a table between a hour difference and if it remains same i do the next task.
For exapmle, here in the below code i want the second select statement to run after 5 seconds.
But when i run this snippet, it waits for 5 seconds and give both the result at the same time.
select count(*) from table1
waitfor delay '0:0:5'
select count(*) from table2
Please advice.
Thanks
Dev

Viewing 9 posts - 1 through 8 (of 8 total)

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

george sibbald SSC Guru Points: 104210 More actions · Answer 1

if you run that in QA it would buffer up the results and return it to you in one go. if you are waiting an hour between checks output the count result to a holding table and compare to that. schedule a job to run once an hour.

---------------------------------------------------------------------

Deviprasad Ten Centuries Points: 1060 More actions · Answer 2

I can not create another table. Yes what i can do it that i can create a global temporary table, but our server reboot schedule is weekely, which will delete the table.

george sibbald SSC Guru Points: 104210 More actions · Answer 3

Dev (7/22/2008)
I can not create another table.

why not?

---------------------------------------------------------------------

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

Dev (7/22/2008)
Hi,
My requirement is to compare count of a table between a hour difference and if it remains same i do the next task.
For exapmle, here in the below code i want the second select statement to run after 5 seconds.
But when i run this snippet, it waits for 5 seconds and give both the result at the same time.
select count(*) from table1
waitfor delay '0:0:5'
select count(*) from table2
Please advice.
Thanks
Dev

George has the right idea. If you run your code with a delay of 1 hour you will be holding a shared lock on table1 for an hour, probably causing some performance issues.

You should have a job that runs your first step and stores the data and then an hour later have a job that runs the second step, comparing it to the first jobs results to determine whether or not you continue.

If you cannot create a table then create the global temp table and just do an if (Object_Id(tempdb.dbo.##table) is null Create ##table end then you are recreating the table as needed. Your table should have 1 column and only 1 row which is the value from the last run. You should delete the value as part of job 2.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question