execute script automatically at 2:00 a.m?

Question

Hello,

I want this procedure to be executed everyday 2:00 a.m. Also, i want to pass date parameter to the procedure. Suppose today is 28th july, 2010, then i want to pass 27th july, 2:00 am to the procedure. This way i want that all records inserted after 27th july, 2:00 am should be backed up in some other table. This is my procedure.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Alter PROCEDURE spBackupRows
(
    @Date datetime
)
AS
BEGIN

    Declare @ItemId int, @ItemName varchar(100), @Description varchar(50), @ItemCreatedDateTime datetime

    Declare myCursor Cursor FOR
        Select * from tblItems Where ItemCreatedDateTime > @Date

    Open myCursor

    Fetch Next From myCursor INTO @ItemId,@ItemName,@Description,@ItemCreatedDateTime

    while @@FETCH_STATUS = 0
    Begin

        INSERT INTO tblBackUpData
        (ItemId,ItemName,Description,ItemCreatedDateTime)
        Values (@ItemId,@ItemName,@Description,@ItemCreatedDateTime)

        Fetch Next From myCursor INTO @ItemId,@ItemName,@Description,@ItemCreatedDateTime

    End

    Close myCursor
    Deallocate myCursor

END
GO

The problem i face is how do i make this procedure fire automatically? and also pass datetime parameter to the procedure?

Thanks in advance :)

Answer 1

How to: Schedule a Job (SQL Server Management Studio)

1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
2. Expand SQL Server Agent, expand Jobs, right-click the job you want to schedule, and click Properties.
3. Select the Schedules page, and then click New.
4. In the Name box, type a name for the new schedule.
5. Clear the Enabled check box if you do not want the schedule to take effect immediately following its creation.

6. For Schedule Type, select one of the following:

   • Click Start automatically when SQL Server Agent starts to start the job when the SQL Server Agent service is started.
   • Click Start whenever the CPUs become idle to start the job when the CPUs reach an idle condition.
   • Click Recurring if you want a schedule to run repeatedly. To set the recurring schedule, complete the Frequency, Daily Frequency, and Duration groups on the dialog.
   • Click One time if you want the schedule to run only once. To set the One time schedule, complete the One-time occurrence group on the dialog.

Answer 2

You can use SQL Server Agent to schedule a job every midnight then you can use the token (DATE)

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

EXEC spBackupRows DATEADD(hour, -22,  $(ESCAPE_NONE(DATE)))

Assuming that you schedule the job @ midnight

Answer 3

For this you should create a schedule and Job, the how to