SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Intro to Database Mail in SQL 2005


Intro to Database Mail in SQL 2005

Author
Message
Clinton Herring
Clinton Herring
Old Hand
Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)

Group: General Forum Members
Points: 340 Visits: 12
Is your operator mail list in SMTP format?



Julie SQL
Julie SQL
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 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!
mvdmeer
mvdmeer
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 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?
rwiethorn
rwiethorn
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 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' Tongue, 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



kirankrapa222
kirankrapa222
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search