How can we scheduled job that can resent all failed emails in the db server?
Hi Experts, Ask SQL Server Central is most solving community in SQL. I liked this Site. I have one doubt. In DatabaseMail, We can See Failed items in dbmail from following table msdb.dbo.sysmail_faileditems My Question is If it have more than one Failed Email count then How we can set loop to resent those failed items using dbmail of msdb.dbo.sp_send_dbmail one by one to make as scheduled job who runs daily? Any one help to give any script to achieve this? Note: I tried script like this but cant finish. so any one help me to complete this script DECLARE @email_id NVARCHAR(450),@id BIGINT,@max_id BIGINT,@query NVARCHAR(1000) ,@recipients NVARCHAR(450) ,@subject NVARCHAR(450) ,@body NVARCHAR(500) SELECT @id=MIN(mailitem_id), @max_id=MAX(mailitem_id) FROM msdb.dbo.sysmail_faileditems WHILE @id<=@max_id BEGIN SELECT @email_id= mailitem_id ,@recipients = recipients , @subject = subject , @body=body FROM msdb.dbo.sysmail_faileditems set @query='msdb.dbo.sp_send_dbmail @
email@example.com'', @recipients='''+@recipients+''', @subject='''+@subject+''', @body='''+@body+'''' EXEC (@query)