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 you try to use these commands in a SQL Server database, it appears that syntax is incorrect. How to solve?
MySQL Event Scheduler is a MySQL-specific feature that is not standardized for other DBMSs (not part of SQL) and thus can not be directly ported to other platforms. However, there are alternatives that do more or less the same thing:
Consider scheduling a task directly on the operating system ( crontab on * NIX, Windows Task Scheduler in Windows) to run these queries. There are those who consider SO the right place ™ for this, and where sysadmins usually expect these things to be put, but not I can speak from experience ...
If you need a solution specific to SQL Server, here's how SQL Server Agent >. I'm not sure if this is the most appropriate way for this type of task, but as pointed out by Fabricio Araujo in comments , its use is common practice in administering MSSQL.
On this page (within the same topic) there is an example that I believe can be adapted for your case (Note: it seems to me, not just run the script and you're done, you need to set everything up properly.) Read the documentation before you put it 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
You can also do this through a query, but using SQL Server Management Studio. See the instructions on the linked page. This question in SOen shows another, simpler example.