SQL Server - How to insert a record once per month using a stored procedure

For this you need to create job and schedule it.

Refer this link for step by step instruction 

http://www.dailycoding.com/Posts/step_by_step_guide_to_add_a_sql_job_in_sql_server_2005.aspx

HI…

After creating the job just execute the below statement which creates a scheduke and attaches the schedule to a job.

You can create a job using SqlserverAgent where you can execute the stored procedure

USE msdb ;

GO

-- creates a schedule named MyScheduleMonthly.

-- Jobs that use this schedule execute Monthly  when the time on the server is 01:00.

EXEC sp_add_schedule

    @schedule_name = N' MyScheduleMonthly ' ,

    @freq_type = 16,

    @freq_interval = 1,

    @active_start_time = 010000 ;

GO

-- attaches the schedule to the job MYJOB

EXEC sp_attach_schedule

   @job_name = N' MYJOB ',

   @schedule_name = N' MyScheduleMonthly ' ;

GO

For this you need to create job scheduling agent for this.

this is an example for this-

USE msdb ;
GO
-- creates a schedule named NightlyJobs.
-- Jobs that use this schedule execute every day when the time on the server is 01:00.
EXEC sp_add_schedule
    @schedule_name = N'NightlyJobs' ,
    @freq_type = 4,
    @freq_interval = 1,
    @active_start_time = 010000 ;
GO
-- attaches the schedule to the job BackupDatabase
EXEC sp_attach_schedule
   @job_name = N'BackupDatabase',
   @schedule_name = N'NightlyJobs' ;
GO


refer this link also-

http://msdn.microsoft.com/en-us/library/ms191439.aspx

Hi Frndz,

 

Functionality:  Run SP at a Once in Month

 

To achieve this task,

 

First Create one Table like SchedularDetail

 

When SP executes then in this table added Current DateTime.

 

After then second time when execute sp then check Date Month & Year Exists in this Scheduler Detail Table.

 

DatePart(month, SchedularDate) = DATEPART(month,getdate())

          AND DatePart(year, SchedularDate) = DATEPART(YEAR,getdate())

 

 

If Date Exists then no need to Do Any operation

 

If Not Exist then do you logic.

 

 

 

Logic   for SP:

 

 

 

CREATE PROCEDURE RunSchedular

 

AS

IF NOT EXISTS

    (

        Select

          SchedularDate

        from

          SchedularDetail

        where

          DatePart(month, SchedularDate) = DATEPART(month,getdate())

          AND DatePart(year, SchedularDate) = DATEPART(YEAR,getdate())

         

    )

BEGIN

   

    INSERT INTO SchedularDetail(SchedularDate) values (GETDATE())

    Print 'SP LOGIC' -- Added Your Other Logic Here

END

ELSE

BEGIN

    Print 'NOT DO ANY OPERATION' -- Not DO any operation

END

 

 

Execute for SP:

 

EXEC RunSchedular

 

Hope this helpful!

Thanks