Need help with trigger? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need help with trigger?

Author

Topic

mrselfdestruct
Starting Member

2 Posts

Posted - 2008-03-07 : 10:05:54

Good morning all.

I'm using dumpevt.exe to gather log files from several servers into a .csv file. From there, I have the following stored procedure perform a bulk insert of the data in the .csv file into a staging databse. From there, it cleans up the data and inserts it into another database. This portion works just fine.

@server char(20)
AS
BEGIN
--Variable declaration
DECLARE @sql varchar(1000)
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.csv /Y', no_output
DELETE FROM tblLogStaging
SET @sql = 'master..xp_cmdshell ''c:\apps\DumpEvt\DumpEvt.exe /logfile=app /outfile=c:\apps\dumpevt\evtlogdump.csv /computer=' + @server + char(39) + ', no_output'
EXEC (@sql)

BULK INSERT tblLogStaging FROM 'c:\apps\dumpevt\evtlogdump.csv'
WITH (FIELDTERMINATOR = ',')

/*Include filter on any column but description here, e.g. EXEC uspFilterApplicationStaging*/
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
/*Include filter on description here */

--Sys log
EXEC master..xp_cmdshell 'copy c:\apps\dumpevt\empty.csv c:\apps\dumpevt\evtlogdump.csv /Y', no_output
DELETE FROM tblLogStaging
SET @sql = 'master..xp_cmdshell ''c:\apps\DumpEvt\DumpEvt.exe /logfile=sys /outfile=c:\apps\dumpevt\evtlogdump.csv /computer=' + @server + char(39)+ ', no_output'
EXEC (@sql)

BULK INSERT tblLogStaging FROM 'c:\apps\dumpevt\evtlogdump.csv'
WITH (FIELDTERMINATOR = ',')

/*Include filter on any column but description here */
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
/*Include filter on description here */

--Clean up
SET CONCAT_NULL_YIELDS_NULL ON
END

However, I would like to be notified via email whenever a record with the Type of '1' (error) is inserted into the database. To achieve this, I've created the following trigger on the tblApplicationLog and tblSystemLog tables. Whenever the store procedure runs with the triggers enabled, the job runs for up to two hours without completing or erroring out. I'm fairly new to working with SQL, so I have no idea where to go next to trouble shoot this. Any suggestions would be greatly appreciated.

FOR INSERT
AS
IF (SELECT COUNT(*) FROM inserted WHERE [type] = '1') > 1
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'profile_name',
@recipients = emailaddress,
@subject = 'Error alert',
@query = 'SELECT description FROM LogTracking.dbo.tblApplicationLog WHERE [type] = 1 AND description IS NOT NULL',
@exclude_query_output=0;
END

JohnC28
Starting Member

11 Posts

Posted - 2008-03-08 : 06:11:25

The code looks OK to me (though do you really need to COUNT(*) ? - why not just one column?).

But regardless, is this the best approach? To send an email for EVERY error inserted? Why not set up a scheduled job to run, say, hourly that does the same query (with a 'AND DATEDIFF(m, time, getdate()) < 60' added to the WHERE clause to only retrieve the last hour's worth) and emails you all the most recent records?

mrselfdestruct
Starting Member

2 Posts

Posted - 2008-03-10 : 12:40:54

John,

Thanks for your help. I've dumped the trigger and made some changes to the stored procedure, though I still need to implement your suggestion of only pulling records for the last hour.

I now recieve the email notification I am looking for, but I am also getting a "duplicate" email, with nothing in the body. Any idea's why?

@server char(20)
AS
BEGIN
--Variable declaration
DECLARE @sql varchar(1000)
--Create HTML table for email
DECLARE @tableHTMLapplication	NVARCHAR(MAX)
DECLARE @tableHTMLsystem	NVARCHAR(MAX);
SET @tableHTMLapplication = 
	N'<H1>Errors in event log</H1>' +
    N'<table border="1">' +
    N'<tr><th>Server Name</th><th>Time</th><th>EventID</th><th>Description</th>' +
    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'</table>' ;
SET @tableHTMLsystem = 
	N'<H1>Errors in event log</H1>' +
    N'<table border="1">' +
    N'<tr><th>Server Name</th><th>Time</th><th>EventID</th><th>Description</th>' +
    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'</table>' ;
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.csv /Y', no_output
DELETE FROM tblLogStaging
SET @sql = 'master..xp_cmdshell ''c:\apps\DumpEvt\DumpEvt.exe /logfile=app /outfile=c:\apps\dumpevt\evtlogdump.csv /computer=' + @server + char(39) + ', no_output'
EXEC (@sql)

BULK INSERT tblLogStaging FROM 'c:\apps\dumpevt\evtlogdump.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.csv /Y', no_output
DELETE FROM tblLogStaging
SET @sql = 'master..xp_cmdshell ''c:\apps\DumpEvt\DumpEvt.exe /logfile=sys /outfile=c:\apps\dumpevt\evtlogdump.csv /computer=' + @server + char(39)+ ', no_output'
EXEC (@sql)

BULK INSERT tblLogStaging FROM 'c:\apps\dumpevt\evtlogdump.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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-10 : 13:09:25

quote:
Originally posted by JohnC28

The code looks OK to me (though do you really need to COUNT(*) ? - why not just one column?).

Well COUNT(*) is the most efficient way to do it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources