How do I create scheduled events in SQL Server, similar to Mysql’s "Event"?

Question

In Mysql I used this syntax to create a scheduled event:

CREATE EVENT 'evento_LimparPaper'
ON SCHEDULE
    EVERY 1 MONTH STARTS '2014-06-3 00:00:01'
ON COMPLETION NOT PRESERVE
ENABLE
COMMENT ''
DO BEGIN
UPDATE 'tbl_Paper' SET capa='mudar';
END

However, when trying to use these commands in an SQL Server database, it appears that the syntax is incorrect. How to solve?

Answer 1

The Mysql Event Scheduler is a Mysql-specific feature, which is not standardized for other DBMS (not part of SQL) and therefore cannot be directly ported to other platforms. However, there are alternatives that do more or less the same thing:

1. Consider scheduling a task directly on the operating system (crontab no *NIX, Windows Task Scheduler on Windows) to run these queries. Some consider the OS the right place for it, and where the sysadmins usually expect these things to be placed, but I cannot speak from experience...

2. If you need a specific solution for SQL Server, see how the SQL Server Agent. I’m not sure if it’s the most appropriate way to do this kind of task, but as per pointed out by Fabricio Araujo in the comments, its use is common practice in MSSQL administration.

   On this page (within the same topic) there is an example that I believe can be adapted to your case (Note: it seems to me, not just run the script and ready; need to configure everything correctly. Read the documentation before putting this into practice):

   USE msdb ;
   GO
   EXEC dbo.sp_add_job
       @job_name = N'Weekly Sales Data Backup' ;
   GO
   EXEC sp_add_jobstep
       @job_name = N'Weekly Sales Data Backup',
       @step_name = N'Set database to read only',
       @subsystem = N'TSQL',
       @command = N'ALTER DATABASE SALES SET READ_ONLY', 
       @retry_attempts = 5,
       @retry_interval = 5 ;
   GO
   EXEC dbo.sp_add_schedule
       @schedule_name = N'RunOnce',
       @freq_type = 1,
       @active_start_time = 233000 ;
   USE msdb ;
   GO
   EXEC sp_attach_schedule
      @job_name = N'Weekly Sales Data Backup',
      @schedule_name = N'RunOnce';
   GO
   EXEC dbo.sp_add_jobserver
       @job_name = N'Weekly Sales Data Backup';
   GO
   

   It is also possible to do this not through a query, but using SQL Server Management Studio. See the instructions on the linked page. That question on the Soen shows another, simpler example.