Intro to Database Mail in SQL 2005

Question

Post reply

Intro to Database Mail in SQL 2005

scb

SSCommitted

Points: 1859
More actions
December 14, 2005 at 5:21 pm

#166262

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/cBunch/introtodatabasemailinsql2005.asp

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply

mick L SSCommitted Points: 1526 More actions · Answer 1

Folks,

I cannot check this out until I get back to the office but, is database mail included with the express edition?

If so it really saves my @ss.

Thanks

Mick

ewilson10 SSCarpal Tunnel Points: 4255 More actions · Answer 2

First let me say that the author did a good job of showing the advantages of the new mail system.

With that said, though, as someone who has fought through SQL 2000 e-mail to the point where there isn't a killer reason to move over (this includes even queing email when the the SQL Server cannot reach the Exchange Server, logging, and auditing), the big issue for me is:

"unless of course for backward compatibility, legacy applications, etc."

Yesterday we heard that complex DTS packages will probably require work and don't hold one's breath for continued DTS support. What are the issues with Mail and what does the future hold?

Sincerely yours,

A late adopter if not skipping 2005 completely

Everett Wilson
ewilson10@yahoo.com

Greg Gonzalez SSC Journeyman Points: 81 More actions · Answer 3

Very nice article on initial Database Mail configuration. I wanted to point out that there are a few more steps required to have SQL Server Agent be able to use Database Mail for jobs, alerts, etc. This is from SQL Server 2005 BOL, "Using Database Mail with SQL Server Agent":

~~Enable Database Mail.~~
~~Create a Database Mail account for the SQL Server Agent service account to use.~~
~~Create a Database Mail profile for the SQL Server Agent service account to use~~ and add the user to the DatabaseMailUserRole in the msdb database.
Set the profile as the default profile for the msdb database.
Choose Database Mail as the mail system for SQL Server Agent. For more information, see How to: Configure SQL Server Agent Mail to Use Database Mail (SQL Server Management Studio).
Restart SQL Server Agent.

(I struck through the steps that were covered in the article, assuming that the account and profile created are Ok to use for SQL Server Agent Mail.)

It's also important to note that SQLMail issues typically fell into a couple of major categories:

Reliability problems
Configuration & maintenance overhead

With SQLMail you could spend a lot of time installing Outlook, creating operators, assigning notifications to jobs, alerts, etc., on each and every SQL Server (Config overhead)... and even then it may or may not work (Reliability)

By taking MAPI/SQLMail out of the mix and going to SMTP with failover Microsoft has addressed biggest element of the "reliability" issue. However, there is still the fact that Database Mail is a distributed system -- if there is some problem with it on one or more SQL Servers, then you may not receive your notifications for those servers. In other words, although each individual Database Mail instance is inherently more reliable than SQLMail when properly configured and maintained, there is still no outside-looking-in process making objective decisions on the health of Database Mail on all servers, which goes towards "enterprise reliability".

In terms of maintenance overhead, I am sure many of those familiar with setup of SQLMail will look at the Database Mail setup & config process and think, "Well, it sure is different... and easier in some respects... but it's still quite a bit of work".

Database Mail still needs to be configured and tested on each SQL Server, operators need to be created, and the DBA must remember to assign notifications to all jobs & alerts, including new jobs & alerts added after initial setup. When you start multiplying this by 50, 100, 250 or more SQL Servers as is commonplace now for many organizations, a migration from 2000/SQLMail to 2005/Database Mail starts to get a bit scary.

Microsoft has certainly made a big move in the right direction with Database Mail, but there is still a ways to go before it can truly be considered an "enterprise friendly" notification solution.

Greg Gonzalez

sqlSentry

ewilson10 SSCarpal Tunnel Points: 4255 More actions · Answer 4

Actually, if SQLMail is set up use a queuing system the reliability issue can be avoided. After playing around with variuos tests the best solution turned out to be rather straight-forward. Once the email is in the queue send the email, if an error is reported then keep the email in the queue and shut down processing of the queue. In three+ years of production no email has been lost despite network/Exchange problems.

Everett Wilson
ewilson10@yahoo.com

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 5

I would add #7 to Greg's list:

7. Issue a statement that send an email.

I use Database Mail and for some reason it works. I am using a statement like that:

EXEC dbo.sp_send_dbmail @recipients='yelena.varshal@philips.com',

@subject = 'Subject of my Email,

@query ='Select * from ##MyTempTable'

@profile_name = 'DBAemail',

@attach_query_result_as_file = 0

I would also add #1.5 if applicable:

1.5 Specify the database.

In my CTP the next screen after you click Setup on the first screen tell you to select a certain database to install Messaging Objects. I do understand that this may change in the release that I am in the process of installing now.

Regards,Yelena Varsha

darren.green SSChasing Mays Points: 641 More actions · Answer 6

"In my CTP the next screen after you click Setup on the first screen tell you to select a certain database to install Messaging Objects" - I suggest you check out the RTM. One change from early CTPs is that Database Mail is now msdb only. The ability to install multiple Database Mail instances has gone in RTM, and probably before, I don't recall exactly.

Darren Green
SQLDTS.com | SQLIS.com | Konesans Ltd

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 7

Darren,

Thanks for your explanations. I am in the process of installing RTM.

Regards,Yelena Varsha

currym SSC Eights! Points: 949 More actions · Answer 8

Thanks for the information. I look forward to 2005 the more I read.

scb SSCommitted Points: 1859 More actions · Answer 9

Great feedback from ewilson & Greg....clearly the organization's needs must be #1. If a lot of time has been invested in implementing SQL Mail, then clearly sticking with that infrastructure would be best, especially the larger the organization....I had hoped my catch-all "unless of course for backward compatibility, legacy applications, etc" captured that. Also, there are indeed extra steps to ensure that job alerts are using database mail...thanks Greg.

Ron West-292827 SSC Veteran Points: 203 More actions · Answer 10

There was one thing missing in the implementation of 2005 database mail: Dynamic Profiles.

The company I work for sends lots of email on behalf of our reseller customers (impersonating them).

It took a ton of work to figure out how to do this and to supress some of the errors that were raised.

There was also some big security settings that needed to be figured out before I could implement this functionality.

since I can't attach the scripts directly, I'll add seperate posts for the steps involved.

Ron West-292827 SSC Veteran Points: 203 More actions · Answer 11

Here's the security setup that's required:

--create the role in msdb

USE [msdb]

GO

/****** Object: DatabaseRole [CanSendEmail] Script Date: 11/03/2005 09:31:08 ******/

CREATE ROLE [CanSendEmail] AUTHORIZATION [dbo]

grant select on sysmail_account to CanSendEmail

grant select on sysmail_profile to CanSendEmail

grant select on sysmail_profileaccount to CanSendEmail

grant execute on sysmail_add_principalprofile_sp to CanSendEmail

grant execute on sysmail_add_profileaccount_sp to CanSendEmail

grant execute on sysmail_add_account_sp to CanSendEmail

grant execute on sysmail_add_profile_sp to CanSendEmail

grant execute on sp_send_dbmail to CanSendEmail

--create the login

CREATE LOGIN [emailonlyuser] WITH <A href="mailtoASSWORD=N's@'">PASSWORD=N'somepassword', DEFAULT_DATABASE=[yourdatabase], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

use yourdatabase

GO

exec sp_adduser 'emailonlyuser'

Grant execute on prcSendmail to emailonlyuser

GO

Use MSDB

GO

exec sp_adduser 'emailonlyuser','emailonlyuser','CanSendEmail'

GO

Ron West-292827 SSC Veteran Points: 203 More actions · Answer 12

Here is the text of the stored procedure:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

/* THIS IS A GENERAL SEND MAIL STORED PROCEDURE

Written by Ron West (ronw@ronwest99.com)

Dynamically creates accounts and profiles for Database

mail in SQL 2005

Make sure you run the permissions script before you create this proc.

*/

Create PROCEDURE [dbo].[prcSendMail]

@Address varchar(max),

@Subject varchar(255),

@Body varchar(max),

@From varchar(50) = 'ops@yourdomain.com',

@FromName varchar(100) = 'SQL Server',

@ReplyTo varchar(100) = 'ops@yourdomain.com',

@BCC varchar(max) = null,

@cc varchar(max) = null,

@Attachment varchar(250) = null,

@HTML bit = 0,

@Sensitivity varchar(12)='Normal',

@Importance varchar(6)='Normal',

@Debug bit=0

AS

BEGIN

SET NOCOUNT ON

Declare @body_format varchar(20),@mailitem_id int

declare @Success bit

declare @response varchar(255),@Name varchar(130)

declare @ProfileName sysname

Declare @AccountID int,@ProfileID int,@mailItemId int

if @Importance not in ('Low','Normal','High')

Set @Importance ='Normal'

if @Sensitivity not in ('Normal','Personal','Private','Confidential')

Set @Sensitivity='Normal'

if ltrim(rtrim(@FromName))='' or @FromName is null

set @FromName=@From

--this will be the key for the Profile AND Account

Set @ProfileName =@From + '/' + @FromName

if @HTML = 1

Set @body_format='HTML'

Else

Set @body_format='TEXT'

/**************************************************

Make sure the account exists and it's part of this profile

**************************************************/

SELECT @AccountID=Account_ID FROM msdb.dbo.sysmail_account WHERE [name]=@profilename

SELECT @ProfileID=Profile_ID FROM msdb.dbo.sysmail_profile WHERE [name]=@profilename

if @Debug=1

Begin

Print '@AccountID=' + isnull(cast(@AccountID as varchar),'was null')

Print '@ProfileID=' + isnull(cast(@ProfileID as varchar),'was null')

End

--create the profile if needed

if @ProfileID is null

Begin

EXEC msdb.dbo.sysmail_add_profile_sp

@profile_name = @ProfileName,

@description = @ProfileName,

@profile_id =@ProfileID output

--give everybody access to use this profile

EXEC msdb.dbo.sysmail_add_principalprofile_sp

@profile_id = @ProfileID,

@principal_name = 'public',

@is_default = 0 ;

End

--create the account (same name as profile) if needed

If @AccountID is null

EXEC msdb.dbo.sysmail_add_account_sp

@account_name = @ProfileName,

@description = @From,

@email_address = @From,

@replyto_address = @ReplyTo,

@display_name = @FromName,

@mailserver_name = 'mail.yourdomain.com',

@Account_Id=@AccountID OUTPUT;

--create the relationship of account to profile

If not exists (Select * from msdb.dbo.sysmail_profileaccount where <A href="mailtorofile_Id=@ProfileID">Profile_Id=@ProfileID and Account_Id=@AccountID)

exec msdb.dbo.sysmail_add_profileaccount_sp

@profile_id =@ProfileID ,

@account_Id=@AccountID,

@sequence_number=1; --first in line

if @Debug=1

Begin

Print '@AccountID=' + isnull(cast(@AccountID as varchar),'was null')

Print '@ProfileID=' + isnull(cast(@ProfileID as varchar),'was null')

End

/**********************************************************

Now, send the mail using the specific account

**********************************************************/

EXEC msdb.dbo.sp_send_dbmail

@profile_name = @ProfileName,

@recipients = @Address,

@subject = @Subject,

@body = @Body,

@Importance=@Importance,

@Sensitivity =@Sensitivity,

@copy_recipients=@CC,

@blind_copy_recipients=@BCC,

@body_format=@body_format,

@file_attachments=@Attachment,

@mailitem_id = @mailitem_id OUTPUT

Return(isnull(@mailitem_id, 0))

END

J-277808 Valued Member Points: 73 More actions · Answer 13

Really expected more from a professional. Although the content of the article was good, it was considerably deficient in providing any substantial content beyond 'point, click and turned on'. OK, well, what next?? . Had it not been for the reply posts, there really was not much to learn from.

THAT SAID: I do appreciate the fact that all of you, including the author, are willing to spend the time to create these posts. But please keep in mind, these posts don't benefit professionals, they benefit those who are learning.

Thank you,

twilson111 SSC Veteran Points: 290 More actions · Answer 14

I appreciated Ron West's followup posting of dynamically setting up email profiles/accounts via TSQL. I was recently questioned on sql05 abilities of producing emails with varible originating names which I know can be done manually. We have an CRM application and the challenge was to create an email from a triggered activity with the contactee's name in the "from" box. Rather than manage hundreds of profiles/accounts manually, the "on-the-fly" approach was the better scenario. Ron's code, with some mods, is just what I'm looking for!

Much Thanks!

Todd Wilson

Td Wilson