SQL :: SendMail

USE[master]
GO
 /****** Object: StoredProcedure [dbo].[sp_dba_SendEmail] ******/

SETANSI_NULLS ON
GOSETQUOTED_IDENTIFIER ON
GOALTERprocedure [dbo].[sp_dba_enviamail] as
declare
@body1varchar(1000),
@logperfnvarchar(1000),
@subjnvarchar(300),
@tBuffnvarchar(256),
@emailnvarchar(256),
@vMSG1nvarchar(MAX),
@vMSG2nvarchar(4000),
@vMSG3nvarchar(4000),
@vMSG4nvarchar(4000),
@vMSG5nvarchar(4000)
set@body1 = 'Acompanhamento: '
-- acompanha o espao em disco dos logs
-- inicio 

CREATE TABLE #tempBuf ( DBName nvarchar(256)
, LogSize int
, LogSpaceUsed int
, LogStatus int
)
INSERT INTO #tempBuf
EXEC ('DBCC SQLPERF(logspace)')
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1>Espaco utilizado pelo log</H1>' +
N'<table border="1">' +
N'<tr><th>BD</th><th>Log (Mb)</th>' +
N'<th>% Log Usado</th>' +
CAST ( ( SELECT td = DBName, '',
td= LogSize, '',
td= LogSpaceUsed
FROM #tempBuf
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
/*
declare curtables cursor for select DBName + '..' + cast(LogSize as varchar) + '..' + cast(LogSpaceUsed as varchar) from #tempBuf
open curtables
fetch curtables into @logperf
set @vMSG1 = ' ';
while @@fetch_status = 0
begin
set @vMSG1 = @vMSG1 + @logperf
fetch curtables into @logperf
end

close curtables
deallocate curtables
*/
set @vMSG1 = @tableHTML
drop table #tempBuf
-- fim
 
DECLARE@ReturnValue nvarchar(1000)
set@vMSG2 = N'<br><br><H1>Espaco livre para cada DB</H1><table>';
EXECEcadTecMovelMidware..sp_dba_monitorabanco @ReturnValue OUTPUT
set@vMSG2 = @vMSG2 + N'<tr><td>DBXXX >> ' + @ReturnValue + N'</td></tr>';
EXECIntermediarioRepl..sp_dba_monitorabanco @ReturnValue OUTPUT
set@vMSG2 = @vMSG2 + N'<tr><td>DBYYY >> ' + @ReturnValue + N'</td></tr>';
EXECSOTI..sp_dba_monitorabanco @ReturnValue OUTPUT
set@vMSG2 = @vMSG2 + N'<tr><td>XPTO >> ' + @ReturnValue + N'</td></tr>';
EXECMaster..sp_dba_monitorabanco @ReturnValue OUTPUT
set@vMSG2 = @vMSG2 + N'<tr><td>Master >> ' + @ReturnValue + N'</td></tr>';
EXECdistribution..sp_dba_monitorabanco @ReturnValue OUTPUT
set@vMSG2 = @vMSG2 + N'<tr><td>Distribution >> ' + @ReturnValue + N'</td></tr>';
EXECtempdb..sp_dba_monitorabanco @ReturnValue OUTPUT
set@vMSG2 = @vMSG2 + N'<tr><td>Tempdb >> ' + @ReturnValue + N'</td></tr>';
EXECMobiControlDB..sp_dba_monitorabanco @ReturnValue OUTPUT
set@vMSG2 = @vMSG2 + N'<tr><td>XXXDB >> ' + @ReturnValue + N'</td></tr>';
set@vMSG2 = @vMSG2 + N'</table><br><br>' ;

-- acompanha o espao em disco local
-- inicio 
declare@corpo nvarchar(4000)
execsp_dba_espaco_em_disco 1, @corpo output
set@vMSG3 = @corpo;
set @email = 'agnicampos@hotmail.com;agni.campos@gmail.com'
set @vMSG1 = @vMSG1 + @vMSG2 + @vMSG3
set @subj = 'Status - ' + @@servername + ' - ' + cast(GETDATE() as nvarchar)
EXEC msdb.dbo.sp_send_dbmail
@profile_name= 'Testes',
@recipients= @email,
@body= @vMSG1,
@subject= @subj,
@body_format= 'HTML'