Intro to Database Mail in SQL 2005

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Corey Bunch » Intro to Database Mail in SQL 2005

25 posts, Page 3 of 3

«««1 2 3

Intro to Database Mail in SQL 2005

Rate Topic

Display Mode

Topic Options

Author

Message

Clinton Herring

Posted Monday, August 13, 2007 11:25 AM

Valued Member

Group: General Forum Members
Last Login: Monday, March 09, 2009 1:35 PM
Points: 60, Visits: 12

Is your operator mail list in SMTP format?

Post #390277

Julie SQL

Posted Tuesday, November 06, 2007 9:08 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, December 10, 2008 11:22 AM
Points: 1, Visits: 8

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!

Post #419416

mvdmeer

Posted Wednesday, November 14, 2007 6:51 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, November 14, 2007 7:27 AM
Points: 1, Visits: 2

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?

Post #422074

rwiethorn

Posted Thursday, December 20, 2007 1:20 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, June 12, 2012 1:59 PM
Points: 7, Visits: 14

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_Status FROM 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_ID INT
DECLARE @Requester_Name nvarchar(50)
DECLARE @Request_App_ID INT
DECLARE @Request_Status varchar(10)
DECLARE @Body nvarchar(255)
DECLARE @App_Owner nvarchar(50)
DECLARE @Recipients nvarchar(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

Post #435377

kirankrapa222

Posted Wednesday, July 16, 2008 1:41 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, July 18, 2008 11:04 PM
Points: 2, Visits: 28

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

Post #534956

« Prev Topic | Next Topic »

25 posts, Page 3 of 3

«««1 2 3

Permissions