Intro to Database Mail in SQL 2005

Question

Intro to Database Mail in SQL 2005

Viewing 10 posts - 16 through 24 (of 24 total)

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

Neile Bermudes-317334 SSC Rookie Points: 43 More actions · Answer 1

Can i just ask something to which i think i already know the answer: if i am managing my SQL 2000 servers in 2005 Management Studio, can i use Database Mail on these servers for SQL Alerting?

Thanks

Div

EdVassie SSC Guru Points: 60472 More actions · Answer 2

Is there anyone out there who has replaced the SQL2005 xp_sendmail with a custom SP that calls sp_send_dbmail? As I only need send capability, I am thinking of doing this so we can loose MAPI-based mail on our SQL Server boxes.

eplacing xp-sendmail with a wrapper for sp_send_dbmail would allow us to de-couple the move to DB Mail from the application changes needed to move away from xp_sendmail.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Clinton Herring Ten Centuries Points: 1160 More actions · Answer 3

If this was stated above anywhere sorry for the repeat...some Virus scanners block port 25 so you might need to add the follow exe to the Virus scanner port 25 exception list (if you don't disable that feature altogether; hopefully not) - DatabaseMail90.exe.

Also, if you have to set up a lot of servers here is some basic code, and I stress basic, that will add a public, default mail profile and account. The Profile name is picked up as the server name and the account name simply adds 'SQL ' to the front of that for an easy Outlook rule check...

Be sure to modify where stated before using.

/** Enables Database mail on SQL 2k5 & Creates a Mail Profile and Account *********/

/*

** assumes no profiles and accounts exist; New SQL 2k5 installtion

** assumes you are creating the first profile and account

** sets this profile as a public, default profile

** I use the <server name> as the profile name

** I use SQL + <server name> as the account name; make for easy Outlook rules

** be sure to add these executables, DatabaseMail90.exe,sqlservr.exe, to the Virus Scanner port 25 exception list

** be sure to add your corporate SMTP relay server name in the 2nd Exec command where needed

** be sure to replace <myplace> with your company domain name (yourcompany.com)

*/

use msdb

exec sp_configure 'Database Mail XPs', 1

reconfigure with override

waitfor delay '00:00:02'

declare @mailprofilename sysname

declare @mailprofileid int

declare @mailaccountname sysname

declare @mailaccountid int

declare @email_address sysname

set @mailprofilename = @@servername -- change as desired

set @mailaccountname = 'SQL ' + @mailprofilename -- change as desired

set @email_address = @mailprofilename + '@<myplace>

-- create a SQL mail profile

Exec dbo.sysmail_add_profile_sp

@mailprofilename

, 'Local default mail profile'

, @mailprofileid output

-- create a SQL mail account

Exec dbo.sysmail_add_account_sp

@mailaccountname

, @email_address

, @mailaccountname

, '' -- reply to address

, 'Local SQL smtp mail account'

, '<corporate SMTP relay server>' -- corporate SMTP relay server

, 'SMTP' -- mail type

, 25 -- port #

, null

, 0 -- 0 anonymous auth, 1 NT auth

, 0 -- 1 enable ssl

, @mailaccountid output

-- associate the SQL mail profile and SQL mail account

Exec dbo.sysmail_add_profileaccount_sp

@mailprofileid

, @mailprofilename

, @mailaccountid

, @mailaccountname

, 1 -- sequence #

-- set the SQL mail profile as public and default

Exec dbo.sysmail_add_principalprofile_sp

2

, 'guest' -- public profile

, @mailprofileid

, @mailprofilename

, 1 -- default profile

go

alfred-169739 SSC Journeyman Points: 93 More actions · Answer 4

This is an excellent article and instruction on setting up Database mail. Very impressed and I was able to setup and have my emailing working after following the article step by step guidelines.

Keep up the good works and continue to share your knowledge.

Alfred owusu(Canada)

Steve-438436 Valued Member Points: 70 More actions · Answer 5

The article is good.

I set up the Database mail account and tested it and it worked fine.

I then set up a maintenance plan with a Notify Operator Task only and executed it to see if the message would be sent to me.

The task failed.

What am I missing?

Clinton Herring Ten Centuries Points: 1160 More actions · Answer 6

Clinton Herring

Ten Centuries

Points: 1160

August 13, 2007 at 11:25 am

#726549

Is your operator mail list in SMTP format?

Julie SQL Valued Member Points: 51 More actions · Answer 7

Does anyone else have the problem that messages generated from database mail get caught in the exchange spam filter? Is there an extra step I need to do to configure it? The DB is on the same intranet, all the users we're emailing are in our own domain, I provided a valid Exchange username when I set up the profile. Our network admin says there's nothing he can do to change the spam filter settings--does anyone have any idea what I should try on the db side?

Thanks very much in advance!

mvdmeer Grasshopper Points: 17 More actions · Answer 8

Good article. Can someone tell me how to use these mailaccounts in jobs? Let's say I make a job that has 2 steps. First is dump transaction log, second is make a backup of a database. Watching the notifications window, it looks like I can only send an E-mail depending of the result of the whole job, not per step. When I try to select someone to send the email to, I can only select an operator. Should I use operators?

rwiethorn SSC Veteran Points: 279 More actions · Answer 9

Great article. I got my Database Mail up in running!

I have a question on how to approach re-sending of Failed Mails.

I'm in the process of developing an app (VB.net, ASPX, and SQL2005). The users updated some tables and I want to send out emails. I have some triggers that fire on the Update of a table.

In testing I noticed 2 emails have failed : "The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2007-12-20T11:48:28). Exception Message: Cannot send mails to mail server. (The specified string is not in the form required for an e-mail address.). )"

Can someone give me ideas on how resend?

I've been told that the SMTP server can go down and up like a yo-yo. My company is supported by 'HP' :P, so we have no infrastructure support

If the server is down then the Trigger is fired, I would expect the email to fail. If this is a the case, is there an error code I can trap for?

If I put the sp_send_DBMail in a Try...Catch and if there is an Error I can write an entry to an exception table, on that I can run a daily job against trying to resend the Mail. I think that would be a good idea, assuming I can trap for an error? Come to think, I would need to know a success code so I can delete the entry from the exception table is the email is sent successfully.

I'm very new to SQL Programming. My experiences are simple selects/updates and deletes with ADO.NET, so my SQL Programming experience is a Newbie.

Here is a snip-it of code from my trigger that is working.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

Alter TRIGGER [dbo].[tr_Request_UPDATE]

ON [dbo].[tblRequest]

AFTER UPDATE

AS

--Check for Request_Status Change

--Possible Changes: Pending B3 Approval --> Approved or Denied

--Possible Changes: Pending B4 Approval --> Approved or Denied

IF NOT UPDATE(Request_Status)

RETURN

--Determine if Request_Status was changed

IF EXISTS (SELECT a.Request_StatusFROM inserted a Where a.Request_Status IN('Approved', 'Denied'))

BEGIN

--Check for Approval or Denied Status

--IF Denied, Send out Email of Denial

--IF Approval, Update tblRegistrations, Insert a new Row with the Appropiate Field.

DECLARE @Request_IDINT

DECLARE @Requester_Namenvarchar(50)

DECLARE @Request_App_IDINT

DECLARE @Request_Statusvarchar(10)

DECLARE @Bodynvarchar(255)

DECLARE @App_Ownernvarchar(50)

DECLARE @Recipientsnvarchar(50)

Select

@Request_ID=a.Request_ID

,@Requester_Name=a.Requester_Name

,@Request_App_ID=a.Request_App_ID

,@Request_Status=a.Request_Status

FROM inserted a Where a.Request_Status IN('Approved', 'Denied')

If @Request_Status='Approved'

BEGIN

--Request Approved

--Send out emails to the Requester and the Application Owner.

Select @App_Owner = App_Owner from dbo.tblApplications where App_ID = @Request_App_ID

Select @Body= 'Your Request for access to the Tool/Application has been approved by'

Select @Recipients= @Requester_Name + '; ' + @App_Owner

--Send the Mail

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'bdc-665_mail',

@recipients = 'rwiethorn@XXXX.XXXX',

--@query = 'Select * From [PSRA].[dbo].[tblRequest] Where Request_ID=12',

@subject = 'Request',

-- @attach_query_result_as_file = 1,

@Body = @Body ,

@body_format='HTML'

END

END;

Any ideas on where to look/read is appreciated,

Thanks,

rwiethorn 'AT' g Mail

kirankrapa222 Valued Member Points: 58 More actions · Answer 10

Hi guys,

I was trying to send a mail using Dataase mail option in sql server.I have configures the user account and profle for that database mail.I could able to send the mails to the user who are under our server, but couldn't send to others(gmail or yahoo).I am getting the following error

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 7 (2008-07-16T12:00:54). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: No such user here). )

Help me out please................

Kiran