Definitions More Info.

Definition ID

786

Title

SQL

Category

NOTES

Definition

SQL mail with HTML+CSS

Definition Description

USE [DBACHECKDB]
GO
/****** Object: StoredProcedure [dbo].[dba_spDBSizeSendMail] Script Date: 16.04.2019 15:20:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[dba_spDBSizeSendMail]
@dbNameMail VARCHAR(100) OUTPUT
AS
-- Compose Mail
DECLARE @IP VARCHAR(100)
,@svrName VARCHAR(255)
,@tableHTML NVARCHAR(MAX)
,@subject1 VARCHAR(200)
,@recipients CHAR(100)
,@subject CHAR(250)
,@body NVARCHAR(MAX)

-- Mail title and server name to print in mail content
SELECT @IP= (SELECT TOP(1) local_net_address
FROM sys.dm_exec_connections
WHERE local_net_address IS NOT NULL)
--SELECT @IP = '10.100.0.18'

SELECT @subject1=@IP+'_'+@@SERVERNAME+' '+@dbNameMail+' '+' DB SIZE CHANGED!!!'
SELECT @tableHTML = N''+
N'

'+@IP+'_'+@@SERVERNAME+''+' '+@dbNameMail+' DB

'+
N''+
N''+
N''+
N''+
N''+
N''+
N''+
N''+
N''+
N''+
N''+
N''+
N''+
N''+
N''+
N''+
N''+
N''+
CAST ( ( SELECT
td = O.[ID], '',
td = O.[DBSizesQueryRelationID], '',
td = O.[Database_id], '',
td = O.[DBName], '',
td = O.[DBFileID], '',
td = O.[DBFileName], '',
td = O.[DBFilePath], '',
td = O.[DBFileSizeMBefore], '',
td = O.[DBFileSizeMBAfter], '',
td = O.[DBLastIncreasedSizeMB], '',
td = O.[DBFileSizeGBefore], '',
td = O.[DBFileSizeGBAfter], '',
td = O.[DBLastIncreasedSizeGB], '',
td = O.[IsShrinked], '',
td = O.[IsInitialSize], '',
td = O.[IsMailed], '',
td = O.[RecordDate], ''
FROM DBACHECKDB.dbo.ufc_MaxIDForDbSizes(@dbNameMail) O
ORDER BY O.DBFileID
FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX)) +
N'

ID	DBSizesQueryRelationID	Database_id	DBName	DBFileID	DBFileName	DBFilePath	DBFileSizeMBefore	DBFileSizeMBAfter	DBLastIncreasedSizeMB	DBFileSizeGBefore	DBFileSizeGBAfter	DBLastIncreasedSizeGB	IsShrinked	IsInitialSize	IsMailed	RecordDate

'+
N'
'+
--N'' +
N'
'+
N'

'+CHAR(13)+CHAR(10)+
N'SELECT * FROM [DBACHECKDB].[dbo].[ufc_DBSizesEffectedByQueries]() '+
N' ile boyut artışına sebep olabilecek sorguları görebilirsiniz. ' +CHAR(13)+CHAR(10)+
N'

';

EXEC msdb.dbo.sp_send_dbmail
@recipients='[email protected]',
--@recipients='[email protected]',
@subject = @subject1,
@body = @tableHTML,
@body_format = 'HTML';

PRINT 'MAIL GÖNDERİLDİ'
UPDATE DBACHECKDB.dbo.ufc_MaxIDForDbSizes (@dbNameMail)
SET IsMailed = 1
WHERE IsMailed = 0

RecordBy	cunay
Record Date	13-07-2017 09:33:15
Düzenle
Kopyala
Sil