Mail Profile sql 2005 : How to retry until successfully sent?

Hi
I recently had a big problem with an important job, after sending an email
using the msdb..sp_send_dbmail stored procedure the job succeeded!
BUT
the emails never went out! because the email server was problematic at
roughly the same time. This is the error that was logged in the sql 2005
email job log.:
No connection could be made because the target machine avtively refused it.
mail server failure ... bla bla bla
When i reran the job a couple of hours later the mails went out because the
mail server was up and running.
My question - is there not a way to tell the mail profile to retry sending
the email a couple of times with say 1 minute intervals between retries -
same as in a sql 2005 job?
thanks
Ian
Hi
As I understood, you may want to PING the server
to check whether or not the server is running up and if it succeded then
send the email
Some ideas, check it out
SET NOCOUNT ON
CREATE TABLE #t_ip (ip varchar(255))
DECLARE @.PingSql varchar(1000)
SELECT @.PingSql = 'ping ' + '00.00.0.0'
INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
BEGIN
DROP TABLE #t_ip
RETURN
END
DROP TABLE #t_ip
"I.W Coetzer" <I.W Coetzer@.discussions.microsoft.com> wrote in message
news:27F55D35-F6A1-437D-888D-B897A30DA9A0@.microsoft.com...
> Hi
> I recently had a big problem with an important job, after sending an email
> using the msdb..sp_send_dbmail stored procedure the job succeeded!
> BUT
> the emails never went out! because the email server was problematic at
> roughly the same time. This is the error that was logged in the sql 2005
> email job log.:
> No connection could be made because the target machine avtively refused
> it.
> mail server failure ... bla bla bla
> When i reran the job a couple of hours later the mails went out because
> the
> mail server was up and running.
> My question - is there not a way to tell the mail profile to retry sending
> the email a couple of times with say 1 minute intervals between retries -
> same as in a sql 2005 job?
> thanks
> Ian
|||You can set the number of retires and the retry delay
interval at the server configuration level. In T-SQL, you
can use sysmail_configure_sp. If you are using the Database
Mail Configuration wizard in Management Studio, select the
option to View or Change System Parameters.
-Sue
On Mon, 16 Apr 2007 04:14:04 -0700, I.W Coetzer <I.W
Coetzer@.discussions.microsoft.com> wrote:

>Hi
>I recently had a big problem with an important job, after sending an email
>using the msdb..sp_send_dbmail stored procedure the job succeeded!
>BUT
>the emails never went out! because the email server was problematic at
>roughly the same time. This is the error that was logged in the sql 2005
>email job log.:
>No connection could be made because the target machine avtively refused it.
>mail server failure ... bla bla bla
>When i reran the job a couple of hours later the mails went out because the
>mail server was up and running.
>My question - is there not a way to tell the mail profile to retry sending
>the email a couple of times with say 1 minute intervals between retries -
>same as in a sql 2005 job?
>thanks
>Ian
|||Hi
This would not have solved the problem because the server was up and running
- but the mail service was hanging / not working i think.
bye
"Uri Dimant" wrote:

> Hi
> As I understood, you may want to PING the server
> to check whether or not the server is running up and if it succeded then
> send the email
> Some ideas, check it out
> SET NOCOUNT ON
> CREATE TABLE #t_ip (ip varchar(255))
> DECLARE @.PingSql varchar(1000)
> SELECT @.PingSql = 'ping ' + '00.00.0.0'
> INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
> IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
> BEGIN
> DROP TABLE #t_ip
> RETURN
> END
> DROP TABLE #t_ip
>
>
> "I.W Coetzer" <I.W Coetzer@.discussions.microsoft.com> wrote in message
> news:27F55D35-F6A1-437D-888D-B897A30DA9A0@.microsoft.com...
>
>
|||brilliant! thank you very much - now to wait and see what happens tomorrow
morning ...
bye
"Sue Hoegemeier" wrote:

> You can set the number of retires and the retry delay
> interval at the server configuration level. In T-SQL, you
> can use sysmail_configure_sp. If you are using the Database
> Mail Configuration wizard in Management Studio, select the
> option to View or Change System Parameters.
> -Sue
> On Mon, 16 Apr 2007 04:14:04 -0700, I.W Coetzer <I.W
> Coetzer@.discussions.microsoft.com> wrote:
>
>