Solved

How do I send an email using database mail?

Posted on 2009-05-19
9
187 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
 
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
Make managing Office 365 email signatures a breeze

Are you using Office 365? Having trouble trying to set up email signatures for your users? Getting stressed out managing multiple signatures? Need an easier way to manage? We have a solution for you, try the most-user friendly and powerful signature management tool on the market.

 
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

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…
Workplace bullying has increased with the use of email and social media. Retain evidence of this with email archiving to protect your employees.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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

863 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

23 Experts available now in Live!

Get 1:1 Help Now