在SQL2005中對發送失敗的Database mail重新發送的SQL

在Database mail Log中找出mailitem_id,然后使用下列sql重新發送

declare @sendmailxml        VARCHAR(max),

        @mailitem_id        int,

        @rc                 INT

 

select @mailitem_id=17--要發送的mailitem_id

 

  -- Create the primary SSB xml maessage

    SET @sendmailxml = '<requests:SendMail xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://schemas.microsoft.com/databasemail/requests RequestTypes.xsd" xmlns:requests="http://schemas.microsoft.com/databasemail/requests"><MailItemId>'

                        + CONVERT(NVARCHAR(20), @mailitem_id) + N'</MailItemId></requests:SendMail>'

 

    -- Send the send request on queue.

    EXEC @rc = msdb.dbo.sp_SendMailQueues @sendmailxml

    IF @rc <> 0

    BEGIN

       RAISERROR(14627, 16, 1, @rc, 'send mail')

    END