stoping sql server agent using store procedures

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 2005 Forums

.NET Inside SQL Server (2005)

stoping sql server agent using store procedures

Author

Topic

harahmani
Starting Member

5 Posts

Posted - 2007-07-10 : 15:21:46

Hi
all i need little help regarding stored procedure.
i have some job say ".one." that runs daily on sql server agent But i have some holiday schedule as well in database
suppose if a holiday come and i want to stop that job on sql server agent on that particular holiday.
can anybody help me writing it ..
how to do it by calling SQL server agent to stop and start with date in stored procedure.
Regards

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-10 : 15:30:48

i don't know why you'd want to stop the whole agent because you can simply not run jobs during holidays.

look into
sp_start_job
sp_stop_job

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-10 : 16:35:51

What would you do if you only want to stop some of the jobs from running on a Holiday but let other jobs run?

If you use a job to stop the SQL Agent, you won't be able to start it again from a job, since jobs will not run.

You can use something like this in a TSQL job step to skip running it on a Holiday.


-- Skip run if today is a holiday
if exists(
	select
		a.*
	from
		MyHolidayTable a
	where
		a.Date = dateadd(dd,datediff(dd,0,getdate()),0) -- Todays date
	)
	goto Do_Not_Run

...
... Code for job
...


Do_Not_Run:

You could also setup a daily job to modify the schedule start date of jobs you do not want to run on a holiday. Just set the schedule start date to the day after the holiday. Look in SQL Server Books Online for sp_update_jobschedule to do this.

CODO ERGO SUM

harahmani
Starting Member

5 Posts

Posted - 2007-07-11 : 15:28:41

OKs i do creating step in job if at first step select query found holiday.
if query succeeded it stop
suppose i have query

Select day from holiday where day=today.
Even When it return null. job quit as query succeeded
i want if job step return some result then it stop the nextstep if my query return null it should continue to second step.
thanks for previous answer was great help

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-11 : 15:35:04

You would have to run the query for every step that you want to control this way.

CODO ERGO SUM

harahmani
Starting Member

5 Posts

Posted - 2007-07-11 : 15:57:12

how make query fails if it returns no value instead of success.
anyway to do it

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-11 : 17:40:22

I do not understand what you are asking.

CODO ERGO SUM

harahmani
Starting Member

5 Posts

Posted - 2007-07-12 : 09:15:23

in jobs edit job step pan i have to past a query which stop executing next step this only way if query fails
but my query return null value and it quits job mean query succeeded. what i do now

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-12 : 10:01:30

I do not understand what you are asking.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources