Solved

How do I send an email using database mail?

Posted on 2009-05-19
9
188 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
php mail headers 2 58
Distribution groups exchange 2013 6 55
My Query is not giving correct result. Please help 5 40
Query to return total 6 19
Utilizing an array to gracefully append to a list of EmailAddresses
Import PST to Exchange using Power Shell new-mailboximportrequest command, you can simply import the PST file into Exchange mailbox or archived. To know How to import PST into Exchange  2013 read the complete article.
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…
The video tutorial explains the basics of the Exchange server Database Availability groups. The components of this video include: 1. Automatic Failover 2. Failover Clustering 3. Active Manager

773 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