Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How do I send an email using database mail?

Posted on 2009-05-19
9
Medium Priority
?
202 Views
Last Modified: 2013-12-17
I have setup database mail on my sql server 2005 database. Now what I want to do is some how use database mail to send an email alert to three different email addresses exactly 1 month before one of my database fields (datetime value) is reached. I also want the alert email to contain other database field information from my database. How do I do this? Stored Procedure? If so, how is the stored procedure triggered? Through my SQL Server Agent?

Thanks.
0
Comment
Question by:Shepwedd
9 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 24422265
0
 

Author Comment

by:Shepwedd
ID: 24422358
Thanks.

However, where exactly does the stored proc get triggered? How will it know to send the email exactly 1 month before my database field date value is reached?
0
 
LVL 80

Expert Comment

by:arnold
ID: 24422500
You should run the stored procedure as a scheduled job.
If the stored procedure has the logic to determine whether the alert should or should not be issued, you would then run the stored procedure on a regular basis. it will generate the email when all the conditions are met.
I.e. you would have a table in the database which this stored procedure will access to determine when it is time to generate the email notification.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 21

Expert Comment

by:Tapan Pattanaik
ID: 24422536
hi Shepwedd,

                    Check these links, every thing is here.

Database Mail in SQL Server 2005:

http://www.databasejournal.com/features/mssql/article.php/3626056/Database-Mail-in-SQL-Server-2005.htm

Configure Database Mail  Send Email From SQL Database:

http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/


Database Mail and SQL Agent Mail setup by using a script:

http://www.mssqltips.com/tip.asp?tip=1736


0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24422592
You'll need to setup a sql server agent job to do run the proc to send the email.
0
 

Author Comment

by:Shepwedd
ID: 24422958
Thanks.

But how do I pull variables into my stored procedure from my database?

I have written the attached but how would I feed the @ClientName and @Amount variables in? Would I just declare them at the top? Surely I have to select them from my tables first? Something like...

ALTER PROCEDURE Payments.proc_ShowClientAmount(@ClientName nvarchar(50), @Amount decimal(6,2))

AS

Select tt.ClientName, ptp.Amount
From Trusts.Trusts tt INNER JOIN Payments.TrustPayments ptp ON tt.TrustID = ptp.TrustID

RETURN

So how do I compine the two?

Thanks.
DECLARE @trust_subject NVARCHAR(100)
DECLARE @trust_message NVARCHAR(MAX)
SET @trust_subject = 'Trust Payment Alert';
SET @trust_message = 'The Client:' + @ClientName + 'has a trust payment due for' + @Amount + 'exactly one month today.';
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'martin.spalding@shepwedd.co.uk', 
@body = @trust_message,
@subject = @trust_subject

Open in new window

0
 
LVL 60

Accepted Solution

by:
chapmandew earned 2000 total points
ID: 24423062
DECLARE @trust_subject NVARCHAR(100)
DECLARE @trust_message NVARCHAR(MAX)
SET @trust_subject = 'Trust Payment Alert';

DECLARE  CursorTemplate CURSOR
      FAST_FORWARD FOR       
      Select
      'The Client:' + tt.clientname + 'has a trust payment due for' + ptp.Amount + 'exactly one month today.';
      From Trusts.Trusts tt INNER JOIN Payments.TrustPayments ptp ON tt.TrustID = ptp.TrustID

OPEN CursorTemplate

FETCH NEXT FROM CursorTemplate
INTO      @trust_message

WHILE (@@FETCH_STATUS = 0)
BEGIN

      EXEC msdb.dbo.sp_send_dbmail
      @recipients = 'martin.spalding@shepwedd.co.uk',
      @body = @trust_message,
      @subject = @trust_subject

      FETCH NEXT FROM CursorTemplate
      INTO      @trust_message

END

CLOSE CursorTemplate
DEALLOCATE CursorTemplate
0
 

Author Comment

by:Shepwedd
ID: 24423287
Thanks but when I go to execute the proc the email doesn't seem to get sent?
0
 

Author Comment

by:Shepwedd
ID: 24423717
My fault. My query was returning nothing. Thanks.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Pop culture is prime bait for hackers seeking to infect user’s computers and mobile devices with malicious malware. Hackers know exactly what the latest trends are online and know how to use them to their advantage.
One-stop solution for Exchange Administrators to address all MS Exchange Server issues, which is known by the name of Stellar Exchange Toolkit.
In this video we show how to create an Accepted Domain in Exchange 2013. We show this process by using the Exchange Admin Center. Log into Exchange Admin Center.: First we need to log into the Exchange Admin Center. Navigate to the Mail Flow >> Ac…
In this Micro Video tutorial you will learn the basics about Database Availability Groups and How to configure one using a live Exchange Server Environment. The video tutorial explains the basics of the Exchange server Database Availability grou…
Suggested Courses

963 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question