How to resend an Auto-import email sent by SQL database mail.

To resend the emails sent out by auto import you will need to do the following.

1.) Find the mailitem_id of the email or emails that need to be resent.  You can do this by looking in the msdb.dbo.sysmail_allitems view.

2.) Once you have the mailitem_id of the email that needs to be resent you will need to execute the msdb.dbo.sp_sendmailqueues stored procedure.  Simply replace the value for @mailitem_id in the query below and then execute the query.  The email should be resent.

 

 

 
declare @mailitem_id int,
@sendmailxml varchar(max),
@rc int

-- Create the primary SSB xml maessage

SET @mailitem_id = 1282    --Replace with mailitem_id

SET @sendmailxml = '' + CONVERT(NVARCHAR(20), @mailitem_id) + N''
 

-- Send the send request on queue.

EXEC @rc = sp_SendMailQueues @sendmailxml

IF @rc <> 0

  BEGIN

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

  END



IF YOU RUN THE ABOVE ACCIDENTALLY, USE THE FOLLOWING TO FIX:

First run this query to check if you have a non-zero number in the queue:

"Use MSDB

Select count(*) from ExternalMailQueue "

 

If you find any entries, run the following to fix:

"Use MSDB

ALTER QUEUE ExternalMailQueue WITH STATUS = ON

set nocount on

declare @Conversation_handle uniqueidentifier;

declare @message_type nvarchar(256);

declare @counter bigint;

declare @counter2 bigint;

set @counter = (select count(*) from ExternalMailQueue)

set @counter2=0

while (@counter2<=@counter)

begin

receive @Conversation_handle = conversation_handle, @message_type = message_type_name from ExternalMailQueue

set @counter2 = @counter2 + 1

end"