June 29, 2006 at 4:40 am
The way that our email system has been set up we can no longer use operators and the old sendmail system stored proc. Due to this I have created a stored proc with a little help from sqlservercentral.com that reads the server log file, then finds the errors, then issues an email. The problem is if I execute the code from query analyser either executing with the code in the analyser screen or stored in stored proc, it reports out the errors. However when I use alerts that look for an error lets say with a severity of 016 which in turns fires the code compiled in the stored proc the data from the log is NOT returned back. I have added the code to help.
CREATE PROCEDURE ProcIssueErrorEmail
AS
DECLARE @vServer VARCHAR(15)
BEGIN
SET NOCOUNT ON
-- Variable declarations
DECLARE @oServer INT, @oQuery INT, @ret INT
DECLARE @maxrow INT, @row INT, @maxcol INT, @col INT
DECLARE @colname VARCHAR(255), @logentry VARCHAR(256), @cont INT
DECLARE @entrytime DATETIME, @logtext VARCHAR(256), @source VARCHAR(20)
DECLARE @vSubject VARCHAR(8000)
CREATE TABLE #logdata (RID SMALLINT IDENTITY(1,1) NOT NULL, EntryTime DATETIME, Source VARCHAR(20), LogEntry VARCHAR(2000), Continued INT)
-- Connect to server
EXEC @ret = master.dbo.sp_OACreate 'SQLDMO.SQLServer', @oServer OUT
EXEC @ret = master.dbo.sp_OASetProperty @oServer, 'LoginSecure', -1
EXEC @ret = master.dbo.sp_OAMethod @oServer, 'Connect', NULL, @@SERVERNAME
-- Get event log
EXEC @ret = master.dbo.sp_OAMethod @oServer, 'ReadErrorLog', @oQuery OUTPUT, 0 -- change the number here to view older logs
-- Read log
EXEC @ret = master.dbo.sp_OAGetProperty @oQuery, 'Rows', @maxrow OUTPUT
EXEC @ret = master.dbo.sp_OAGetProperty @oQuery, 'Columns', @maxcol OUTPUT
SET @row = 0
WHILE @row < @maxrow
BEGIN
SET @row = @row + 1
EXEC @ret = master.dbo.sp_OAGetProperty @oQuery, 'GetColumnString', @logentry OUTPUT, @row, 1
EXEC @ret = master.dbo.sp_OAGetProperty @oQuery, 'GetColumnDouble', @cont OUTPUT, @row, 2
IF (ISDATE(LEFT(@logentry,22)) = 1) OR (@cont <> 0)
BEGIN
IF (@cont = 0)
BEGIN
SET @entrytime = CONVERT(datetime,LEFT(@logentry,22),121)
SET @source = SUBSTRING(@logentry,24,9)
SET @logtext = RIGHT(@logentry,LEN(@logentry)-32)
INSERT INTO #logdata (EntryTime, Source, LogEntry, Continued)
VALUES (@entrytime, @source, @logtext, @cont)
END
ELSE
BEGIN
SET @logtext = @logentry
UPDATE #logdata SET LogEntry = LogEntry + @logtext
WHERE Entrytime = @entrytime AND Source = @source AND Continued = 0
END
END
END
--Build subject string
SET @vSubject = 'All there has been an error on the above server, below is a list of errors that have occured in the last hour. This list has been sorted in a descending order.' + CHAR(13) + Char(13)
-- Display log entries
SELECT @vSubject = @vSubject + ' ' + CAST(A.EntryTime AS VARCHAR(20)), @vSubject = @vSubject + ' ' + A.Source, @vSubject = @vSubject + ' ' + A.LogEntry + B.LogEntry + CHAR(13)
FROM #logdata AS A
INNER JOIN #logdata AS B
ON A.RID = B.RID - 1
WHERE A.LogEntry LIKE '%Severity:%'
AND A.EntryTime BETWEEN DATEADD(MI, -60, GETDATE()) AND GETDATE()
ORDER BY A.EntryTime DESC
-- Cleanup
EXEC master.dbo.sp_OADestroy @oServer
EXEC master.dbo.sp_OADestroy @oQuery
DROP TABLE #logdata
SET @vSubject = @vSubject + CHAR(13) + CHAR(13) + 'This email has been produced by the server automatically, so please do not reply to the above address. THANK YOU'
SELECT @vServer = CAST(SERVERPROPERTY('machinename') AS VARCHAR(15))
exec master.dbo.xp_smtp_sendmail
@FROM = @vServer,
@TO = 'xxxxx@xxx.com',
@subject = 'Error found on above server',
@message = @vSubject,
@server = 'xxxxxxxxxxxxxxxx',
@type='text/plain'
SET NOCOUNT OFF
END
GO
Result when being called from alert:
All there has been an error on the above server, below is a list of errors that have occured in the last hour. This list has been sorted in a descending order.
This email has been produced by the server automatically, so please do not reply to the above address. THANK YOU
Result when being called from Analyser:
All there has been an error on the above server, below is a list of errors that have occured in the last hour. This list has been sorted in a descending order.
Jun 29 2006 11:00AM spid64 Error: 50000, Severity: 16, State: 1 test.
This email has been produced by the server automatically, so please do not reply to the above address. THANK YOU
Any help to explain this and fix it would be very helpful.
Cheers all
June 30, 2006 at 1:50 am
Try changing your select line to:
SELECT @vSubject = @vSubject + ' ' + CAST(ISNULL(A.EntryTime,'') AS VARCHAR(20)) + ' ' + ISNULL(A.Source,'') + ' ' + ISNULL(A.LogEntry,'') + ISNULL(B.LogEntry,'') + CHAR(13)
Please note that if any of these columns do not allow NULL, then you can remove the ISNULL(,'') additions I made.
One column assignment to a variable is not available to other "columns" in the select statement, so each is in effect overwiting the next.
I would suggest use of a different variable like @vQuery for your SELECT so you can PRINT @vSubject, @vQuery to assist in debugging.
Andy
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply