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