wiating till the procedure complete

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

wiating till the procedure complete

Author

Topic

ammyhus
Starting Member

8 Posts

Posted - 2012-01-15 : 06:23:20

hi all ,

i am currently waiting for 5 seconds before i rerun the procedure dbo.main_s1_proc1. i would like to wait until the procedue get over before it is ran again. is there a way to achive this.

thanks , below is my code for running the procedure after every 5 seconds.

while 0 = 0
begin
exec dbo.Main_s1_Proc1
if @@error = 0
WAITFOR DELAY '000:00:05'
else
break
end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-15 : 09:55:02

why not add a sql agent job to call this and schedule it to ran every 5 mins. each time it will check if previous run is over and then only it will run

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ammyhus
Starting Member

8 Posts

Posted - 2012-01-15 : 23:38:27

the only problem is that i would want it to run with a 5 second delay after completion of each run. i understand that SQL Agent job does not allow you to run anything less than 60 seconds.

Thanks & Regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 01:00:45

thats not true. you can have schedule in sql agent which is less than 60 seconds
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ammyhus
Starting Member

8 Posts

Posted - 2012-01-16 : 01:27:07

i have tried using the schedule option in the Job agent the daily frequency just gives 2 options. hours and minute. will appriciate if you could let me know what option do i use to get it to 5 seconds or 10 second delay.

Thanks & Regards

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-01-16 : 01:47:48

You can use :
msdb.dbo.sp_add_jobschedule or
msdb.dbo.sp_update_jobschedule

to acheive the seconds duration.

I wouldn't recommend using SQL Server Agent Job for "real-time" activity.

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 01:53:36

quote:
Originally posted by ammyhus

i have tried using the schedule option in the Job agent the daily frequency just gives 2 options. hours and minute. will appriciate if you could let me know what option do i use to get it to 5 seconds or 10 second delay.

Thanks & Regards

10 second delay you can set schedule properties

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2012-01-16 : 04:12:21

quote:
Originally posted by ammyhus

the schedule option in the Job agent the daily frequency just gives 2 options. hours and minute

I think SQL 2000 was like that, but SQL 2008 allows Seconds.

What's wrong with your current method where you just delay 5 seconds after each iteration?

ammyhus
Starting Member

8 Posts

Posted - 2012-01-16 : 04:36:39

the current method runs with out waiting for the job to complete. its just starts the job. waits for 5 seconds and starts another one. in case the 1st job is not completed then the second job crashes.

Kristen
Test

22859 Posts

Posted - 2012-01-16 : 05:29:07

quote:
Originally posted by ammyhus

the current method runs with out waiting for the job to complete. its just starts the job. waits for 5 seconds and starts another one. in case the 1st job is not completed then the second job crashes.

You sure?

This method, which you posted earlier, will run the Sproc, wait for it to finish, then delay a further 5 seconds, then loop round


while 0 = 0
begin
    exec dbo.Main_s1_Proc1
    if @@error = 0
        WAITFOR DELAY '000:00:05'
    else
        break
    end
end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 05:29:46

quote:
Originally posted by ammyhus

the current method runs with out waiting for the job to complete. its just starts the job. waits for 5 seconds and starts another one. in case the 1st job is not completed then the second job crashes.

thats not true. the same job wont be started by agent unless previous run is finished. So if last instance is running, it wont fire it again. It will check for next run only after next scheduled gap

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources