asked
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 @
profile_name=''xx@xx.com'', @recipients='''+@recipients+''', @subject='''+@subject+''', @body='''+@body+'''' EXEC (@query)
Hello! Try this DECLARE @email_id INT, @id INT = 1, @query NVARCHAR(max), @recipients NVARCHAR(max), @subject NVARCHAR(max), @body NVARCHAR(max) DECLARE @FailedList TABLE ( ID INT IDENTITY(1,1), EmailID INT, Recipients NVARCHAR(max), EmailSubject NVARCHAR(max), EmailBody NVARCHAR(max) ) INSERT INTO @FailedList (EmailID, Recipients, EmailSubject, EmailBody) SELECT mailitem_ID, recipients, [subject], body from msdb.dbo.sysmail_faileditems WHILE (1=1) BEGIN SELECT @email_id= EmailID, @recipients = Recipients, @subject = EmailSubject, @body=EmailBody FROM @FailedList Where ID = @id IF (@@ROWCOUNT=0) BREAK; set @query='msdb.dbo.sp_send_dbmail @
profile_name=''xx@xx.com'', @recipients='''+@recipients+''', @subject='''+@subject+''', @body='''+@body+'''' EXEC(@query) -- PRINT(@query) SET @id+=1 END
14 People are following this question.
