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'
, 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
Alter TRIGGER [dbo].[tr_Request_UPDATE]
--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)
--Determine if Request_Status was changed
IF EXISTS (SELECT a.Request_Status FROM inserted a Where a.Request_Status IN('Approved', 'Denied'))
--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)
FROM inserted a Where a.Request_Status IN('Approved', 'Denied')
--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
@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 ,
Any ideas on where to look/read is appreciated,
rwiethorn 'AT' g Mail