Bashirsk CFC Bashirsk CFC - 4 months ago 16
SQL Question

Specify sp_add_schedule (Transact-SQL)

I am trying to do time intervals using the add schedule of Microsoft SQL server 2008. I am being specific and i want the time to run

weekly once sunday midnight
. My
@freq_type
is 8 since its running weekly and the
@freq_subday_type
is 1 since i want it to run at a specific time. However this can also be applied to
daily once midnight
. I dont want it to look the same. How can i make the sunday one to be more specific, specifying that it has to be sunday which is a specific day.

Daily Once Midnight

EXEC sp_add_schedule
@schedule_name = N'UTC +3h Daily Once Midnight' ,
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 1 ,
@freq_subday_interval = 0,
@active_start_time=210000
GO


Weekly once Sunday midnight

EXEC sp_add_schedule
@schedule_name = N'UTC +6h Weekly Once Sunday Midnight' ,
@freq_type = 8,
@freq_interval = 1,
@freq_subday_type = 1 ,
@freq_subday_interval = 0,
@active_start_time=180000
GO

Answer

I think that the second schedule shall be as follows:

USE msdb ;  
GO 

EXEC sp_add_schedule  
  @schedule_name = N'UTC +6h Weekly Once Sunday Midnight' ,  
  @freq_type = 8,  
  @freq_interval = 1,
  @freq_recurrence_factor=1, 
  @freq_subday_type = 1 ,
  @freq_subday_interval = 0,
  @active_start_time=180000        
Go

Good Luck.