T-SQL：如何創建每月執行的作業

我已經知道如何使用 來安排每月的工作SQL Server Agent > Jobs，我現在想學習如何使用 T-SQL 來安排這樣的工作。

我知道如何每天在特定時間安排工作：

CREATE PROCEDURE MyTask
AS
BEGIN  
   SET NOCOUNT ON;
   -- Now is set to run evry day at 23:00
   -- But I need to run it every 1th of the month 
   declare @delayTime nvarchar(50)
   set @delayTime = '23:00'

   while 1 = 1
   begin
       waitfor time @delayTime 
       begin
           -- Here the query I want to run every month
           select * from AdventureWorks2012.dbo.AWBuildVersion;
       end
   end
END

-- Sets stored procedure for automatic execution.
sp_procoption    @ProcName = 'MyTask',
               @OptionName = 'startup',
               @OptionValue = 'on' 

在此Microsoft 指南中，我看到我可以使用@freq_type = 16，但我不知道如何設置它，非常感謝您提供一個範例

要安排一個作業每月執行一次，在這種情況下是第一天的午夜：

USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Get Date',
   @enabled=1, 
   @notify_level_eventlog=0, 
   @notify_level_email=0, 
   @notify_level_netsend=0, 
   @notify_level_page=0, 
   @delete_level=0, 
   @category_name=N'[Uncategorized (Local)]', 
   @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Get today''s date', 
   @step_id=1, 
   @cmdexec_success_code=0, 
   @on_success_action=1, 
   @on_success_step_id=0, 
   @on_fail_action=2, 
   @on_fail_step_id=0, 
   @retry_attempts=0, 
   @retry_interval=0, 
   @os_run_priority=0, @subsystem=N'TSQL', 
   @command=N'Select getdate()', 
   @database_name=N'master', 
   @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'First day of month', 
   @enabled=1, 
   @freq_type=16, 
   @freq_interval=1, 
   @freq_subday_type=1, 
   @freq_subday_interval=0, 
   @freq_relative_interval=0, 
   @freq_recurrence_factor=1, 
   @active_start_date=20180312, 
   @active_end_date=99991231, 
   @active_start_time=0, 
   @active_end_time=235959, 
   @schedule_uid=N'7e73cf5c-8036-41a2-af0d-3b2151862684'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
   IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

引用自：https://dba.stackexchange.com/questions/199965