create procedure usp_send_replication_alerts
as
begin
set nocount on
declare @subscriber_ip_address varchar(1000)
declare @subscriber_ip_name varchar(1000)
declare @alerterrormessage varchar(1000)
declare @alerterrormessagecount int
declare @general_name varchar(100)
declare @i int
declare @max_count int
declare @subscribername varchar(1000)
declare @patindexname int
declare @count int
declare @final_subscriber_name varchar(1000)
declare @left_subscriber_name varchar(100)
declare @alert_message_patindexname int
declare @count_alert_message int
declare @left_alert_error_message varchar(1000)
declare @length_alert_message int
declare @final_count int
declare @time datetime
declare @j int
declare @mail_body varchar(1000)
declare @mail_subject varchar(1000) 
declare @message varchar(1000)
 
create table #replicationalerts
(
replication_alert_id int,
[time] datetime,
subscriber varchar(100),
alert_error_message varchar(1000)
)
create table #subscriber_alert_message
(
subscriber_name varchar(1000),
general_name varchar(100),
[time] datetime,
alert_message varchar(1000)
)
insert #replicationalerts
select row_number() over(order by alert_id),[time],subscriber,alert_error_text
from sysreplicationalerts
where datepart(yyyy,[time]) = datepart(yyyy,getdate())
and datepart(mm,[time]) = datepart(mm,getdate())
and datepart(dd,[time]) = datepart(dd,getdate())
set @i = 1
select @max_count = max(replication_alert_id) from #replicationalerts 
while(@i<=@max_count)
begin
select 
@subscribername=subscriber from #replicationalerts where replication_alert_id = @i
select 
@patindexname=patindex('%\%',@subscribername)
select 
@count = @patindexname-1
select 
@left_subscriber_name = left(@subscribername,@count) from #replicationalerts
select 
@alerterrormessage = alert_error_message from #replicationalerts where replication_alert_id = @i
select 
@alert_message_patindexname = patindex('%.%',@alerterrormessage)
select 
@count_alert_message = @alert_message_patindexname -1
select 
@length_alert_message = len(alert_error_message) from #replicationalerts where replication_alert_id = @i
select 
@final_count = @length_alert_message-@count_alert_message
select 
@left_alert_error_message = right(@alerterrormessage,@final_count-1) from #replicationalerts
select 
@time = [time] from #replicationalerts where replication_alert_id = @i
insert #subscriber_alert_message
select @left_subscriber_name,NULL,@time,@left_alert_error_message
update #subscriber_alert_message
set general_name = a.general_name from notification_plants a inner join #subscriber_alert_message b 
on a.plant_server_name = b.subscriber_name
set @i = @i+1
end
create table #alert_message
(
row_id int,
subscriber_name varchar(1000),
general_name varchar(1000),
alert_message varchar(1000)
)
insert #alert_message
select 
rank() over(order by subscriber_name,alert_message),
subscriber_name, 
general_name, 
alert_message
from 
#subscriber_alert_message
where 
datediff(mi,[time],getdate())<=60
set @j =1
select @max_count = max(row_id) from #alert_message 
while(@j<=@max_count)
begin
select @mail_subject = general_name+space(1)+'Replication Has Failed' from #alert_message where row_id = @j
select @message = alert_message from #alert_message where row_id = @j
select @mail_body = 
'
Hi Team,<br><br>
The reason for failure is:<br><br> 
'+convert(varchar(100),@message)+'<br><br>
Regards,<br>
BCP Administrator
'
exec msdb..sp_send_dbmail @profile_name = 'Profile Name' 
,@recipients ='Email Address'
,@subject = @mail_subject
,@body = @mail_body 
,@body_format = 'HTML' 
,@importance = 'High' 
set @j = @j+1
end
drop table #alert_message
drop table #replicationalerts
drop table #subscriber_alert_message
end