How to delete a row after certain time

Question

User-1664020322 posted

Hi,

I have a table which has three columns UID, Created Date and time, Last Login Date and Time. Is there a way where I can delete a record automatically after 24 hours of its creation and Last Login Date and Time.

I'm able to insert the Last Login Date and time, Created Date and Time with ASP.NET. So, now I want a query where I can delete it after 24 hours of its creation and Login.

Thank You!

Any help would be appreciated.

Regards,

Sandeep 

Answer 1

User-654786183 posted

You can do that with SQL Server Agent.  Simply create a job with a regular schedule and the SQL you want to run. There is an easy to use wizard interface for job creation.

Refer this link

Answer 2

User-1664020322 posted

Thank you very much, I really appreciate it for responding back to me quickly.

So, to have access with SQL Server Agent do I need to login with SQL Server Authentication? Because when I log in with Windows Authentication I do not see SQL Server Agent. 

I also wrote a stored procedure for deleting the record.

Thank You!

Regards,

Sandeep

Answer 3

User-654786183 posted

I think the problem is not with the login.  You may be using SQL Server Express and that doesn't include SQL Server Agent, so it's not possible to just create SQL Agent jobs.

when you run select serverproperty('edition') and if returns Express edition, you have to upgrade to get SQL Server Agent

Refer this for an alternative solution

Answer 4

User-183374066 posted

Hi Sandeep,

You can put your code in console and attach the exe to windows scheduler which can run it after some interval.

You can make a windows service which can call the database to do the job

You can use SQL server Schedule Job.

You can use timer class to run your function on regular interval. Put this in global.asax application_start() to call a function on regular basis. But this is not a recommended approach as application pool can be reset at any time. And your code will not run if you app idle for longer period.

Sharing some links which surely can help

https://msdn.microsoft.com/en-us/library/ms191439.aspx?f=255&MSPPError=-2147217396

http://stackoverflow.com/questions/4329859/how-to-call-function-on-timer-asp-net-mvc

http://stackoverflow.com/questions/1211335/method-calls-with-time-interval-asp-net-c

http://stackoverflow.com/questions/12080471/how-to-call-a-method-at-specific-interval-of-time-in-net-mvc