SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Create Sql Job


Create Sql Job

Author
Message
mahenpali
mahenpali
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 52
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'
Steve Jones
Steve Jones
SSC Guru
SSC Guru (248K reputation)SSC Guru (248K reputation)SSC Guru (248K reputation)SSC Guru (248K reputation)SSC Guru (248K reputation)SSC Guru (248K reputation)SSC Guru (248K reputation)SSC Guru (248K reputation)

Group: Administrators
Points: 248193 Visits: 19805
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
My Blog: www.voiceofthedba.com
mahenpali
mahenpali
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 52
yes sir that's my problem
Silverfox
Silverfox
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10262 Visits: 1161
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
Silverfox
Silverfox
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10262 Visits: 1161
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
mahenpali
mahenpali
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 52
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
vky3
vky3
SSC-Addicted
SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)

Group: General Forum Members
Points: 436 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,
Silverfox
Silverfox
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10262 Visits: 1161
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search