Create Sql Job

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Create Sql Job

Create Sql Job

Rate Topic

Display Mode

Topic Options

Author

Message

mahenpali

Posted Tuesday, September 08, 2009 7:35 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, June 05, 2012 1:22 AM
Points: 12, Visits: 49

Hello All

i would like to create a sql job which execute my define code per minute but it's not working properly
when i shutdown my PC,and on next day i start my pc the next start day will forward to 3 days

please help me out i use following code

USE msdb ;

EXEC dbo.sp_add_job
@job_name = N'ExecuteGreedyTran',
@enabled = 1,
@description = N'Execute transactions in per one min'

exec sp_add_jobschedule
@job_name =N'ExecuteGreedyTran',
@name = N'ExecuteGreedyTran',
@freq_type =4,@freq_interval =4,
@freq_subday_type =0x4,@freq_subday_interval=1, @active_start_date =20090821,
@active_end_date =20091228,
@active_start_time =091400

EXEC sp_add_jobstep
@job_name = N'ExecuteGreedyTran',
@step_name = N'ExecuteGreedyTran',
@subsystem = N'TSQL',
@command = N'USE test insert into ztest values (1,''1 job'',''2 job'')',
@retry_attempts = 5,
@retry_interval = 5 ;
GO

exec sp_add_jobserver @job_name = 'ExecuteGreedyTran'

Post #784240

Steve Jones - SSC Editor

Posted Tuesday, September 08, 2009 8:15 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 3:30 PM
Points: 31,436, Visits: 13,751

What's not working properly? when you reboot it is set to start 3 days from the boot?

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #784282

mahenpali

Posted Wednesday, September 09, 2009 4:32 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, June 05, 2012 1:22 AM
Points: 12, Visits: 49

yes sir that's my problem

Post #784776

Silverfox

Posted Wednesday, September 09, 2009 5:35 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 6:50 AM
Points: 2,719, Visits: 1,065

Occurs every 4 day(s) every 1 minute(s) between 09:14:00 and 23:59:59. Schedule will be used between 21/08/2009 and 28/12/2009.

you need to change it to

Occurs every day every 1 minute(s) between 09:14:00 and 23:59:59. Schedule will be used between 21/08/2009 and 28/12/2009.

view the job schedule properties and change the recurs every:

from 4 days

to 1 days

--------------------------------------------------------------------------------------
Recommended Articles on How to help us help you and
solve commonly asked questions

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden
Managing Transaction Logs by Gail Shaw
How to post Performance problems by Gail Shaw
Help, my database is corrupt. Now what? by Gail Shaw

Post #784827

Silverfox

Posted Wednesday, September 09, 2009 5:37 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 6:50 AM
Points: 2,719, Visits: 1,065

on another note, if you want it to work only on a working week.

change it to weekly and just select the days that you want it to run on.

--------------------------------------------------------------------------------------
Recommended Articles on How to help us help you and
solve commonly asked questions

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden
Managing Transaction Logs by Gail Shaw
How to post Performance problems by Gail Shaw
Help, my database is corrupt. Now what? by Gail Shaw

Post #784829

mahenpali

Posted Friday, September 18, 2009 6:48 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, June 05, 2012 1:22 AM
Points: 12, Visits: 49

Any one help me
how can i do that using Code
i am not well aware about interface

please tell what am i doing wrong

Thanks
mahendra singh rajpurohit

Post #790332

vky3

Posted Friday, September 18, 2009 8:47 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 4:26 PM
Points: 124, Visits: 642

Replace your code in sp_add_jobschedule with the following. I think it would work.

@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=1,
@freq_relative_interval=0,
@freq_recurrence_factor=1,

Post #790448

Silverfox

Posted Friday, September 18, 2009 9:06 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 6:50 AM
Points: 2,719, Visits: 1,065

This will fix it for you, before I get shouted at for using a deprecated command, I know it is sql 2000, but it works.

sp_update_jobschedule @job_Name= 'ExecuteGreedyTran',@Name= 'ExecuteGreedyTran', @freq_interval = 1

--------------------------------------------------------------------------------------
Recommended Articles on How to help us help you and
solve commonly asked questions

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden
Managing Transaction Logs by Gail Shaw
How to post Performance problems by Gail Shaw
Help, my database is corrupt. Now what? by Gail Shaw

Post #790458

« Prev Topic | Next Topic »

Permissions