Some applications have batch jobs that send emails to users based on some criteria. There is no user interface to this, so a stored procedures that run as schedule jobs in SQL Server 2005 works well. An example of this is a reminder feature of your application. Maybe you want to remind all users to enter their time sheets on Friday afternoons. Below is an overview of how to write the sql for the lines in the stored procedure that can be used in the schedule job.
For more information on using a cursor see my blog entry at: http://justgeeks.blogspot.com/2008/08/using-t-sql-cursor-in-sql-server.html For more informaiton on configuring SQL Server Database Mail check out http://www.mssqltips.com/tip.asp?tip=1100 or do a search on Google for something like: setting up database mail. There are lots of articles on the subject. Please note that this code will only work on SQL 2005 (and maybe newer version, but definitely not SQL 2000). There is a lot of options for sp_send_dbmail. I recommend checking out the parameters, etc if you need more information. See http://msdn.microsoft.com/en-us/library/ms190307.aspx Here is the code to send email to a selection of people.
Declare @FName as nvarchar(50) Declare @LName as nvarchar(100) Declare @Email as nvarchar(255) Declare @MySubject as nvarchar(128) Declare @Message as nvarchar(MAX) Declare PersonCursor CURSOR FAST_FORWARD FOR Select fname, lname, email from Person where city = 'Phoenix' order by fname OPEN PersonCursor FETCH NEXT FROM PersonCursor INTO @FName, @LName, @Email WHILE @@FETCH_STATUS = 0 BEGIN -- send the email Set @Message = 'This is a test email.' Set @MySubject = 'Hi ' + @FName + ' ' + @FName EXEC msdb.dbo.sp_send_dbmail @profile_name = 'MyProfile', @recipients = @Email, @subject = @MySubject, @body = @Message FETCH NEXT FROM PersonCursor INTO @FName, @LName, @Email END CLOSE PersonCursor DEALLOCATE PersonCursor
2 comments:
hi while excuting this code i got an error Incorrect syntax near 'msdb'. and Incorrect syntax near the keyword 'CLOSE'. ithink the exec word is missing?i m new to this so i m not sure.
but i want to ask u this is the only thing we have to do or we have to make some other settings also i want to use this for sending reminder emails we dont need to pass server name in it?
HI shweta,
Oops, I guess I always change the @subject to something else before I run it so I never got that error. And very good catch, it appears exec is required for the call to msdb.dbo.sp_send_dbmail and I had not put that in the code (Oops!). I have updated the code to reflect the changes. Thank you very much for your feedback. I feel a bit sheepish having broken code online.
One last thing, you will need to change the select statement to something that is useful to you. You may also need to add or take away variables from the FETCH NEXT FROM lines (it is in there twice so you MUST change in both places)
Post a Comment