USE [msdb]
GO
ALTER procedure [dbo].[xxx_sp_check_commandlog] @rcpto nvarchar(200), @debug int = 0
AS
BEGIN
------------------------------------------------------------------------
-- --- author: Marcus Deubel --- --
-- --- copyright: (c) 2019 Marcus Deubel --- --
-- --- license: BSD License --- --
-- --- description: stored procedure to check commandlog table --- --
-- --- : and send emails on error messages --- --
------------------------------------------------------------------------
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
set nocount on;
declare @xml nvarchar(max) = N'';
declare @table nvarchar(max);
declare @header nvarchar(max);
declare @errNo int;
declare @database nvarchar(10);
declare @sqlstmt nvarchar(512);
declare @lsubject nvarchar(200);
declare @servername nvarchar(200);
declare @reboottime datetime;
set @servername = @@SERVERNAME;
set @reboottime = CONVERT(nvarchar(24), GETDATE(), 120);
----------------------------
-- original T-SQL Abfrage --
----------------------------
--select ID, '',
-- DatabaseName, '',
-- isnull(SchemaName, 'n/a'),
-- isnull(ObjectName, 'n/a'),
-- isnull(ObjectType, 'n/a'),
-- isnull(IndexName, 'n/a'),
-- isnull(convert(nvarchar,IndexType), 'n/a'),
-- isnull(StatisticsName, 'n/a'),
-- isnull(convert(nvarchar,PartitionNumber), 'n/a'),
-- isnull(ExtendedInfo, 'n/a'),
-- isnull(Command, 'n/a'),
-- isnull(CommandType, 'n/a'),
-- isnull(convert(nvarchar,StartTime), 'n/a'),
-- isnull(convert(nvarchar,EndTime), 'n/a'),
-- isnull(convert(nvarchar,ErrorNumber), 'n/a'),
-- isnull(ErrorMessage, 'no Error')
--FROM [msdb].[dbo].[CommandLog] where CommandType = 'DBCC_CHECKDB' and convert(date,StartTime) = convert(date, getdate());
set @errNo = (select errorNumber from msdb.dbo.CommandLog where CommandType = 'DBCC_CHECKDB' and convert(date,StartTime) = convert(date, getdate()) and ErrorNumber > 0);
set @header = N'<html>' +
N'<style>' +
N'table, th, td { border: 1px solid black; }' +
N'</style>' +
N'</head>' +
N'<body>' +
N'<H3>CommandLog</H3>' +
N'<table border="1">' +
N'<tr>' +
N'<th>DB ID</th><th>Database Name</th>' +
-- N'<th>SchemaName</th><th>ObjectName</th><th>ObjectType</th><th>IndexName</th><th>IndexType</th><th>StatisticsName</th><th>PartitionNumber</th><th>ExtendedInfo</th>' +
N'<th>Command</th><th>CommandType</th><th>StartTime</th><th>EndTime</th><th>ErrorNumber</th><th>ErrorMessage</th>' +
N'</tr>';
set @xml = cast ((select td = ID, '',
td = DatabaseName, '',
--td = isnull(SchemaName, 'n/a'), '',
--td = isnull(ObjectName, 'n/a'), '',
--td = isnull(ObjectType, 'n/a'), '',
--td = isnull(IndexName, 'n/a'), '',
--td = isnull(convert(nvarchar,IndexType), 'n/a'), '',
--td = isnull(StatisticsName, 'n/a'), '',
--td = isnull(convert(nvarchar,PartitionNumber), 'n/a'), '',
--td = isnull(ExtendedInfo, 'n/a'), '',
td = isnull(Command, 'n/a'), '',
td = isnull(CommandType, 'n/a'), '',
td = isnull(convert(nvarchar,StartTime), 'n/a'), '',
td = isnull(convert(nvarchar,EndTime), 'n/a'), '',
td = isnull(convert(nvarchar,ErrorNumber), 'n/a'), '',
td = isnull(ErrorMessage, 'no Error Message'), ''
FROM [msdb].[dbo].[CommandLog] where CommandType = 'DBCC_CHECKDB' and convert(date,StartTime) = convert(date, getdate())
FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX));
set @table = @header + @xml + N'</table></body></html>'
if @debug = 1
begin
set @errNo = 1;
end;
if @errNo > 0
begin
set @lsubject = @servername + N': CommandLog - DBCC CHECKDB ERROR - Please check' + N' - ' + CONVERT(nvarchar(24), GETDATE(), 120);
end
else
begin
set @lsubject = @servername + N': CommandLog - DBCC CHECKDB OK' + N' - ' + CONVERT(nvarchar(24), GETDATE(), 120);
end
if len(@xml) > 0 or @debug = 1
begin
exec msdb.dbo.sp_send_dbmail @profile_name = 'Default Public Profile',
@recipients = @rcpto,
@subject = @lsubject,
@body = @table,
@body_format = 'HTML' ;
end
END
