Creating process doing specific job periodically

Creating process doing specific job periodically

• kubmg

  hi,

  remedios_ wrote:

  Hi, can I create a background process in SQL Server doing specific jobs periodically Stored procedure is the only solution

  SQLExpress does not provide the SQL Server Agent, the native SQL Server scheduler where specific jobs can be scheduled to execute specific "task", such as Transact-SQL statements, CmdExec OS commands and the like..
  obviously the full blown SQL Server 2005 editions provides this component..
  this is not as "creating a background process", as the Agent runs a proprietary process and related threads..

  a solution can even be to schedule, via the native OS scheduler (AT, SCHTASKS) the required tasks.. this is usualy the solution for scheduled backups in SQLExpress scenarios...

  now the question is about what you have to do and on which "platform", SQLExpress or full blown SQL Server editions..

  regards

  
  
  

• Alvin Kuiper

  Hi, Andrea.
  
  I'm using SQL 2005 Express edition.
  There is a table which saves data from clients every other minute. This table keeps only one hour's data before from current time. So I must delete the expired data. This is the task that I want to schedule.
  
  I don't exactly know which is efficiant that doing in my application or let SQL Server does.
  
  I'll visit the site you commented and thank you very much.

  
  
  

• PeteJM01

  Hi,

  I'd go for an OS scheduled task, running SQLCmd.exe to execute the desired Transact-SQL statement...

  something like

  SET NOCOUNT ON;
  USE tempdb;
  GO
  CREATE TABLE dbo.History (
  	Id int NOT NULL IDENTITY,
  	data varchar(25) DEFAULT 'will be removed',
  	Registered datetime NOT NULL DEFAULT DATEADD(Hour, -1, GETDATE())
  	);
  GO
  WHILE (SELECT COUNT(*) FROM dbo.History) < 10 BEGIN
  	INSERT INTO dbo.History DEFAULT VALUES;
  END;
  INSERT INTO dbo.History VALUES ( 'this will not be removed', GETDATE());
  GO
  SELECT * FROM dbo.History;
  GO
  PRINT 'actual statement to be scheduled every hour';
  DELETE FROM dbo.History
  	WHERE DATEDIFF(Hour, Registered, GETDATE()) >= 1;
  SELECT * FROM dbo.History;
  GO
  DROP TABLE dbo.History;
  --<-----------------
  Id     data           Registered
  ----------- ------------------------- -----------------------
  1      will be removed      2006-12-09 12:03:35.843
  2      will be removed      2006-12-09 12:03:35.860
  3      will be removed      2006-12-09 12:03:35.860
  4      will be removed      2006-12-09 12:03:35.860
  5      will be removed      2006-12-09 12:03:35.860
  6      will be removed      2006-12-09 12:03:35.860
  7      will be removed      2006-12-09 12:03:35.860
  8      will be removed      2006-12-09 12:03:35.860
  9      will be removed      2006-12-09 12:03:35.860
  10     will be removed      2006-12-09 12:03:35.860
  11     this will not be removed 2006-12-09 13:03:35.860
  
  actual statement to be scheduled every hour
  Id     data           Registered
  ----------- ------------------------- -----------------------
  11     this will not be removed 2006-12-09 13:03:35.860
  
  

  the actual "cleaning" statement should then be included in a cmd file like

  SQLCmd -S(Local) -E -dTheDatabase -Q"DELETE FROM dbo.History WHERE DATEDIFF(Hour, Registered, GETDATE()) >= 1;" >c:\MyLog\ScheduledCleanup.txt

  schedule it as desired..

  regards

  
  
  
• Creating process doing specific job periodically