Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Create Sql Job Expand / Collapse
Author
Message
Posted Tuesday, September 8, 2009 7:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 5, 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
Posted Tuesday, September 8, 2009 8:15 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 11:24 AM
Points: 33,088, Visits: 15,197
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
Posted Wednesday, September 9, 2009 4:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 5, 2012 1:22 AM
Points: 12, Visits: 49
yes sir that's my problem
Post #784776
Posted Wednesday, September 9, 2009 5:35 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, July 26, 2014 11:00 AM
Points: 2,728, Visits: 1,115
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
Posted Wednesday, September 9, 2009 5:37 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, July 26, 2014 11:00 AM
Points: 2,728, Visits: 1,115
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
Posted Friday, September 18, 2009 6:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 5, 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
Posted Friday, September 18, 2009 8:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Friday, September 18, 2009 9:06 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, July 26, 2014 11:00 AM
Points: 2,728, Visits: 1,115
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 »

Add to briefcase

Permissions Expand / Collapse