/*

-- Alterar o profile e o e-mail para envio do alerta
@profile_name = 'NOME PROFILE',
@recipients = 'SEU EMAIL',

*/
CREATE procedure [dbo].[stpAlerta_Erro_Replicacao]
AS
BEGIN
	
	declare @Qtd_Minutos int = 30, @Qtd_Erros int 

	if(object_id('TempDb..#replicacao_erros') is not null) drop table #replicacao_erros

	select time, error_text
	into #replicacao_erros
	from distribution.dbo.MSrepl_errors
	where	time >= dateadd(minute, -@Qtd_Minutos, getdate())
			and error_code <> ''
	order by time desc
	
	select @Qtd_Erros = count(*) from #replicacao_erros
	
	if (@Qtd_Erros > 0)
	begin					
		-- Declara as variaveis
		DECLARE @importance as varchar(6), @EmailBody VARCHAR(MAX), @AlertaErroReplicacaoHeader VARCHAR(MAX), @subject varchar(500), 
				@AlertaErroReplicacaoTable VARCHAR(MAX), @emptybody2 VARCHAR(MAX), @recipientsList varchar(8000)

		/**************************************************************
		-- Queries Lentas - Header
		***************************************************************/
		SET @AlertaErroReplicacaoHeader = '<font 
					color=black bold=true size= 5>'
		            
		SET @AlertaErroReplicacaoHeader = @AlertaErroReplicacaoHeader + '<BR /> Erros Replicacao <BR />' 
		SET @AlertaErroReplicacaoHeader = @AlertaErroReplicacaoHeader + '</font>'

		/**************************************************************
		-- Queries Lentas - Informacoes
		***************************************************************/	 
		SET @AlertaErroReplicacaoTable= cast( (    
			SELECT td = time + 
						'</td><td>' +  error_text
			FROM (  
					select convert(varchar(20),time,120) as time, cast(error_text as varchar(300)) as error_text
					from #replicacao_erros
					
				  ) as d order by time 
		  FOR XML PATH( 'tr' ), type ) AS VARCHAR(MAX) 
		)   
		      
		SET @AlertaErroReplicacaoTable = REPLACE( replace( replace( @AlertaErroReplicacaoTable, '&lt;', '<' ), '&gt;', '>' )    , '<td>', '<td align = center>')
		    
		SET @AlertaErroReplicacaoTable= '<table cellpadding="0" cellspacing="0" border="3" >'    
				  + '<tr>
				  <th width="100">Horario</th>
				  <th width="800">Texto Erro</th>			  
				  </tr>'    
				  + replace( replace( @AlertaErroReplicacaoTable, '&lt;', '<' ), '&gt;', '>' )   
				  + '</table>' 
				
		SET @emptybody2=''  
		SET @emptybody2 = '<table cellpadding="5" cellspacing="5" border="0">'    
					  + 
					  '<tr>
					  <th width="500">               </th>
					  </tr>'    
					  + REPLACE( REPLACE( isnull(@emptybody2,''), '&lt;', '<' ), '&gt;', '>' )   
					  + '</table>'    

		/**************************************************************
		-- Sending Email
		***************************************************************/
		              
		SET @importance ='High'     

		SET @subject =	'ALERTA: Ocorreram ' + cast(@Qtd_Erros as varchar) + ' erros de replicacao no servidor ' +  @@servername + 
						' nos ultimos ' + cast(@Qtd_Minutos as varchar) + ' minutos'

		SELECT @EmailBody = @AlertaErroReplicacaoHeader + @emptybody2 + @AlertaErroReplicacaoTable + @emptybody2 -- Block
		  		
		EXEC msdb.dbo.sp_send_dbmail    
				@profile_name = 'NOME PROFILE',
				@recipients = 'SEU EMAIL',
				@subject = @subject ,    
				@body = @EmailBody ,    
				@body_format = 'HTML' ,    
				@importance=@importance		
	end
END