Creating process doing specific job periodically

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

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

|||

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.

|||

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