SQL Server : scheduling programmatically

Question

I'm trying to force SQL Server to call a needed stored procedure each hour.

I've read the following articles:

They are quite big and not very straight forward.

Can anyone confirm that the following SQL code is right for my task:

USE msdb ;
GO
EXEC dbo.sp_add_job
    @job_name = N'Exec RemoveOldCsvImportData' ;
GO
EXEC sp_add_jobstep
    @job_name = N'Exec RemoveOldCsvImportData',
    @step_name = N'execute stored procedure',
    @subsystem = N'TSQL',
    @command = N'exec RemoveOldCsvImportData', 
    @retry_attempts = 5,
    @retry_interval = 5 ;
GO
EXEC dbo.sp_add_schedule
    @schedule_name = N'RunOnce',
    @freq_type = 4,
    @freq_interval = 1,
    @freq_subday_type = 0x8,
    @freq_subday_interval = 1,
    @active_start_time = 233000 ;
USE msdb ;
GO
EXEC sp_attach_schedule
   @job_name = N'Exec RemoveOldCsvImportData',
   @schedule_name = N'RunOnce';
GO
EXEC dbo.sp_add_jobserver
    @job_name = N'Exec RemoveOldCsvImportData';
GO

I love the idea of a schedule called `'RunOnce'` that actually runs things on a regular basis... — Damien_The_Unbeliever, Jul 12 '12 at 10:41

score 4 · Answer 1 · answered Jul 12 '12 at 10:20

My suggestion would be to create the job through the wizard, script it out, and then use that for your script. Creating a job like you're trying to do is an arcane process. You'll get it eventually, but if this is a one-shot, the wizard route will save you a lot of time.

score 2 · Answer 2 · answered Jul 12 '12 at 09:34

2

Apart from the spurious USE msdb ; in the middle, it looks good, and it runs successfully. Why not just try running it? You can always delete the job again if it's not quite what you want.

answered Jul 12 '12 at 09:34

David M

71,481
13
158
186

Josien · Answer 3 · 2012-07-12T11:13:21.590

Executing this code on my SQL Server instance gives me as a schedule 'Occurs every day every 1 hour(s) between 23:30:00 and 23:59:59', and I think that is not what you want.

Can anyone confirm that the following SQL code is right for my task

Answer: nope, it's not right.

Like Ben Thul and David M are suggesting in their answers: you can just try the code and check the job to see if it does what you want, and it would be even better to create a new job by right-clicking on Jobs in the SQL Server Agent and choosing New Job....

score 0 · Answer 4 · answered Jul 12 '12 at 10:41

You can generate script from sql job agent and run it its a one way. The other way is to generate task using Window Task Schedule from the wizard. for that go to my computer --> right click on it and select Manage option. it will open server manager. now go to configuration option and select Task Scheduler. you can just create task from create task option right side of the form in action tab. its very easy way to generate job from here. Actually i also using same thing for my project requirement.

In that i just create task that will execute one store procedure every day at 12:00 once. So you can also do same way if not run that script and do all the stuff in script instead.

SQL Server : scheduling programmatically

4 Answers4