March 11, 2008 at 8:18 am
Morning all,
I'm importing server event logs into a database, then using sp_send_dbmail to send out notifications when events that are errors are inserted. I was previously running the SELECT statement to find the errors in the sp_send_dmail block, but the messages were practically unreadable. I created HTML tables and use database mail to send the HTML table instead. This works great, however I am now getting duplicate messages with no content, and can't seem to figure out why. I'm including the stored procedure that imports the logs and sends the messages.
Thanks,
Gary
@server char(20)
AS
BEGIN
--Variable declaration
DECLARE @sql varchar(1000)
DECLARE @tableHTMLapplicationNVARCHAR(MAX)
DECLARE @tableHTMLsystemNVARCHAR(MAX);
SET @tableHTMLapplication =
N' ' +
N' ' +
N' ' +
CAST (( SELECT td=[Server],'',
td=[Time],'',
td=[EventID],'',
td=[description]
FROM LogTracking.dbo.tblApplicationLog
WHERE [Type] = 1 AND [Description] IS NOT NULL
ORDER BY [Time] DESC
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N' ' ;
SET @tableHTMLsystem =
N' ' +
N' ' +
N' ' +
CAST (( SELECT td=[Server],'',
td=[Time],'',
td=[EventID],'',
td=[description]
FROM LogTracking.dbo.tblSystemLog
WHERE [Type] = 1 AND [Description] IS NOT NULL
ORDER BY [Time] DESC
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N' ' ;
SET NOCOUNT ON
SET CONCAT_NULL_YIELDS_NULL OFF
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
--Main
--App log
EXEC master..xp_cmdshell 'copy c:\apps\dumpevt\empty.csv c:\apps\dumpevt\evtlogdump_app.csv /Y', no_output
DELETE FROM tblLogStaging
SET @sql = 'master..xp_cmdshell ''c:\apps\DumpEvt\DumpEvt.exe /logfile=app /outfile=c:\apps\dumpevt\evtlogdump_app.csv /computer=' + @server + char(39) + ', no_output'
EXEC (@sql)
BULK INSERT tblLogStaging FROM 'c:\apps\dumpevt\evtlogdump_app.csv'
WITH (FIELDTERMINATOR = ',')
INSERT INTO tblApplicationLog ([Log], [Time], [Source], [EventID], [Type], [Category], [User], [Server], [Description])
SELECT [Log], [Time], [Source], [EventID], [Type], [Category], [User], [Server], Fragment0 + Fragment1 + Fragment2 + Fragment3 + Fragment4 + fragment5 + Fragment6
FROM tblLogStaging
--Sys log
EXEC master..xp_cmdshell 'copy c:\apps\dumpevt\empty.csv c:\apps\dumpevt\evtlogdump_sys.csv /Y', no_output
DELETE FROM tblLogStaging
SET @sql = 'master..xp_cmdshell ''c:\apps\DumpEvt\DumpEvt.exe /logfile=sys /outfile=c:\apps\dumpevt\evtlogdump_sys.csv /computer=' + @server + char(39)+ ', no_output'
EXEC (@sql)
BULK INSERT tblLogStaging FROM 'c:\apps\dumpevt\evtlogdump_sys.csv'
WITH (FIELDTERMINATOR = ',')
INSERT INTO tblSystemLog ([Log], [Time], [Source], [EventID], [Type], [Category], [User], [Server], [Description])
SELECT [Log], [Time], [Source], [EventID], [Type], [Category], [User], [Server], Fragment0 + Fragment1 + Fragment2 + Fragment3 + Fragment4 + Fragment5 + Fragment6
FROM tblLogStaging
--Send email notification
EXEC msdb.dbo.sp_send_dbmail
@recipients='email_address',
@profile_name = 'profile',
@subject = 'Application Log Errors',
@body = @tableHTMLapplication,
@body_format = 'HTML' ;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'profile',
@recipients='email_address',
@subject = 'System Log Errors',
@body = @tableHTMLsystem,
@body_format = 'HTML';
--Clean up
SET CONCAT_NULL_YIELDS_NULL ON
END
March 11, 2008 at 11:43 am
I see you creating the @tableHTMLapplication at the top of the procedure, then importing the data, then I see to executions of the sp_send_dbmail procedure.
You are creating the body of the message prior to importing the data, and you are sending the empty data twice after completing the import (no processing of data after the insert).
😎
March 11, 2008 at 12:39 pm
Thanks for the assist.
What I found to be causing the duplication that I was seeing was using the sp_send_dbmail inside that stored procedure. Another stored procedure runs the stored procedure that is listed for every server listed in another table, so it was sending an application log email and system log email for each server listed. I moved the HTML table and database code into another stored procedure to be fired off once the first is finished, and this resolved the problem.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply