Stored procedures/triggers to automatically run 1

[morgancaroline]

I want to write a stored procedure or trigger that will automatically delete records more than two months old. I was wondering if there's a way to run a procedure automatically say every evening as oppose to it running when someone visits the page.
Thanks

 

[salma]

The stored procedure is easy enough to create using the getdate() function to find data older than two months

You can then create a job in SQLExecutive (accessed from Enterprise Manager) to run your sp and create a schedule to run it daily.

The HOW TO section of SQL Server Books Online details how to create jobs/tasks.

hope this helps,
Salma

 

[morgancaroline]

Another quick query regarding this matter - at the start of each day, I'm getting the sp to look in a table to see whose birthday it is. If it finds any bithdays, is it then possible to open an asp in which a program is written in VBscript sending an e-mail saying happy birthday. At the moment someone has to go into the page so that the code is run and the e-mail sent, hence if noone opens the page that day, no e-mails are sent and if more than one person opens the page, a happy birthday e-mail is sent a number of times.
Thanks again

 

[salma]

not too sure this is what you want but ...
you can run VBScript code using the Windows Scripting Host (WSH) engine (CSRIPT.EXE). This is available as a free download from Microsoft's website and once installed and registered, allows you to run any VBScript from a file. All you need to do is save the script file with a .vbs extension.

You can then use xp_cmdshell from SQLExecutive to execute the file.

There may be syntax differences when running the script using WSH so check this out first. There's a good help file on MS site on how to use WSH

hope this helps,
Salma

 

[DuncFair]

You could do it all in your SP if you add a call to xp_sendmail at the end of your procedure - something like this:

-------------------------------------------
exec master.dbo. xp_sendmail @recipients = @contact_email,
@subject = 'Happy Birthday!',
@message = @text

-------------------------------------------

where you have populated @text with a 'custom' template that includes basic variables like @name_first, @date_birth, etc.

Good Luck,
Dunc

 

[morgancaroline]

Thankyou to you both, your suggestions have been most helpful.

Cali