Solved

How do I send an email using database mail?

Posted on 2009-05-19
9
186 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
Comment Utility
0
 

Author Comment

by:Shepwedd
Comment Utility
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 76

Expert Comment

by:arnold
Comment Utility
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
 
LVL 21

Expert Comment

by:Tapan Pattanaik
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 60

Expert Comment

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

Author Comment

by:Shepwedd
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
Thanks but when I go to execute the proc the email doesn't seem to get sent?
0
 

Author Comment

by:Shepwedd
Comment Utility
My fault. My query was returning nothing. Thanks.
0

Featured Post

Shouldn't all users have the same email signature?

You wouldn't let your users design their own business cards, would you? So, why do you let them design their own email signatures? Think of the damage they could be doing to your brand reputation! Choose the easy way to manage set up and add email signatures for all users.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Nearly six years ago I was hired by a company to be their senior server engineer. One of my first projects was to implement Exchange Server 2007 on a Windows Server 2008 Single Copy Cluster for high availability. That was the easy part; read on to l…
In this video we show how to create a Distribution Group 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 Recipients >>…
In this video we show how to create an Address List 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 Organization >> Ad…

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now