Solved

How do I send an email using database mail?

Posted on 2009-05-19
9
190 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 77

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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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 500 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Utilizing an array to gracefully append to a list of EmailAddresses
As cyber crime continues to grow in both numbers and sophistication, a troubling trend of optimization has emerged over the last year.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

791 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