Excute Job with T-SQL - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Excute Job with T-SQL

Author

Topic

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2009-07-19 : 21:02:07

hey people!
i'm just trying to figure out a way to basically have a job that runs continuously... or even polls kinda like replication does, where it waits (x) seconds, and then runs again.

i know i can set the schedualer to every minute, but that seems to be the lowest setting, and i'm looking for more like 10-30 seconds so i was thinking of just starting the job, and step two could just call the job again, or something.

any ideas would help!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-19 : 21:07:33

loop your procedure several time and use "waitfor delay" in between execution

KH
[spoiler]Time is always against us[/spoiler]

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2009-07-19 : 21:08:49

can you give me some ideas on how to do that?

never did delays before

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2009-07-19 : 21:13:12

okay, i think i found some stuff....

is there any way to have the wait on the 2nd step, and have it call the 1st step after the delay?

WAITFOR DELAY '00:00:10' right?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-19 : 21:21:24

1. create the job that execute every 1 minute
2. in the steps
exec yoursp
WAITFOR DELAY '00:00:10'
exec yoursp
WAITFOR DELAY '00:00:10'
exec yoursp

repeat the above .. exec . . wait for in the step

KH
[spoiler]Time is always against us[/spoiler]

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2009-07-19 : 21:26:07

yeah, it's not an SP, it's just t-sql in the step, but that's okay, i can change that...

so you think, use sp's and simply:


call sp
wait
call sp
wait
etc.

while loop=1
   begin
     call sp
     wait
   end

what u think?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-19 : 21:29:57

don't use while loop. have your job scheduled to execute every 1 minute

KH
[spoiler]Time is always against us[/spoiler]

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2009-07-19 : 22:37:38

OHOH one quick question, is there a way i can though a variable see what server is running something.

for example:

BEGIN CATCH
	set @subjectl = 'Failed Job' + @xorderid
	set @bodyl = 'Failed Job!
User Name	' + @xusername + '        
Email	      ' + @xusername + '
Database	MyTable
'+@sql + '

Error on ' + @@whatserverthisis
	EXEC msdb.dbo.sp_send_dbmail 
		@recipients='albert@natimark.com', 
		@subject = @subjectl, 
		@body = @bodyl, 
		@profile_name = @xsource
END CATCH

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2009-07-19 : 22:40:11

LOL n/m it's @@servername. thanks ne way though!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources