May 25, 2017 at 8:21 am
Hi all,
I want to add a couple of features to my code, to add a flag column and then add "If...Then" logic to a query.
The code below creates a temp table, queries the error log, and if it finds any entries with a Severity 16 (or more) level entry, copies any qualifying entry into the temp table,
If there are no qualifying rows in the error log, I'd like all processing to stop, the code returns nothing.
If there are any qualifying rows, then Print 'Errors Found' with the corresponding rows, then on subsequent iterations, IGNORE those rows, either by populating a flag column, or creating a second table and joining against that - which I think will be a resource hog!). I am very open to ideas how to accomplish.
The following is as far as I've got, you can see at the bottom where I got stuck!! .....
--**----------------------------------------------------------------------**
--Poll SQL Error Log and Return Significant Errors
--Author: Jaybeesql
--Version: 1.0
--**----------------------------------------------------------------------**
USE Master;
GO
CREATE TABLE [dbo].[#TmpErrorLog] (
[LogDate] DATETIME NULL
,[ProcessInfo] VARCHAR(20) NULL
,[Text] VARCHAR(MAX) NULL
,[Flag] Int(2) NULL
);
-- Command will insert the error log data into a temporary table
INSERT INTO #TmpErrorLog
(
[LogDate]
,[ProcessInfo]
,[Text]
)
EXEC master.dbo.xp_readerrorlog 0 , 1, ", Severity: 16, State:"
EXEC master.dbo.xp_readerrorlog 0 , 1, ", Severity: 17, State:"
EXEC master.dbo.xp_readerrorlog 0 , 1, ", Severity: 18, State:"
EXEC master.dbo.xp_readerrorlog 0 , 1, ", Severity: 19, State:"
EXEC master.dbo.xp_readerrorlog 0 , 1, ", Severity: 20, State:"
EXEC master.dbo.xp_readerrorlog 0 , 1, ", Severity: 21, State:"
EXEC master.dbo.xp_readerrorlog 0 , 1, ", Severity: 22, State:"
EXEC master.dbo.xp_readerrorlog 0 , 1, ", Severity: 23, State:"
EXEC master.dbo.xp_readerrorlog 0 , 1, ", Severity: 24, State:"
EXEC master.dbo.xp_readerrorlog 0 , 1, ", Severity: 25, State:"
-- retrieve data from temporary table
Select Case
When (Select COUNT(*) From #TmpErrorLog) = 0
Else (Select *
From #TmpErrorLog)
May 30, 2017 at 2:30 am
Hi all - can anyone help with this?
May 30, 2017 at 3:16 am
A case statement returns expressions (constants, functions or variables), not select statements
If you want to not query the table at all if it's empty, then you need an IF statement block. not a CASE.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 30, 2017 at 3:17 am
To be honest I haven't really read your post properly, but from scanning I think you maybe looking for this.
USE Master;
GO
CREATE TABLE [dbo].[#TmpErrorLog] (
[LogDate] DATETIME NULL
,[ProcessInfo] VARCHAR(20) NULL
,[Text] VARCHAR(MAX) NULL
,[Flag] Int
);
-- Command will insert the error log data into a temporary table
INSERT INTO #TmpErrorLog
(
[LogDate]
,[ProcessInfo]
,[Text]
)
EXEC master.dbo.xp_readerrorlog 0 , 1, ", Severity: 16, State:"
EXEC master.dbo.xp_readerrorlog 0 , 1, ", Severity: 17, State:"
EXEC master.dbo.xp_readerrorlog 0 , 1, ", Severity: 18, State:"
EXEC master.dbo.xp_readerrorlog 0 , 1, ", Severity: 19, State:"
EXEC master.dbo.xp_readerrorlog 0 , 1, ", Severity: 20, State:"
EXEC master.dbo.xp_readerrorlog 0 , 1, ", Severity: 21, State:"
EXEC master.dbo.xp_readerrorlog 0 , 1, ", Severity: 22, State:"
EXEC master.dbo.xp_readerrorlog 0 , 1, ", Severity: 23, State:"
EXEC master.dbo.xp_readerrorlog 0 , 1, ", Severity: 24, State:"
EXEC master.dbo.xp_readerrorlog 0 , 1, ", Severity: 25, State:"
-- retrieve data from temporary table
IF (Select COUNT(*) From #TmpErrorLog) = 0
Print 'Empty'
else (Select *
From #TmpErrorLog)
Drop table #TmpErrorLog
May 30, 2017 at 4:19 am
With regard to ignoring rows already processed in subsequent iterations, xp_readerrorlog includes a start date parameter. You can log in a table each use of your process, and then include logic to only query the errorlog for rows later than the most recent date in that table.
John
May 30, 2017 at 12:39 pm
GilaMonster - Tuesday, May 30, 2017 3:16 AMA case statement returns expressions (constants, functions or variables), not select statementsIf you want to not query the table at all if it's empty, then you need an IF statement block. not a CASE.
To add to Gail's comment, the CASE statement isn't even properly formatted. It needs a THEN keyword after every WHEN keyword and the CASE statement resides within the SELECT, not surrounding it, unless the SELECT inside it happens to be a subquery.
Such as SELECT CASE WHEN COUNT(*) = 0 THEN 'Empty'
WHEN (SELECT TOP 1 LogDate FROM #TmpErrorLog WHERE Flag = 16) THEN 'Okay, Houston, We have a problem'
ELSE Text END
FROM #TmpErrorLog
Granted, I don't know what your data really looks like, so the above CASE is just filler to show you how you use a subquery SELECT. You'd have to tweak it to make it do what you need. It looks like, as others have mentioned, you really want an IF statement though.
May 30, 2017 at 2:53 pm
If the goal is to terminate all processing, why not just exit the procedure?
IF (SELECT COUNT(*) FROM #TmpErrorLog) = 0
BEGIN
RETURN;
END;
SELECT stuff...
May 30, 2017 at 7:48 pm
Ed Wagner - Tuesday, May 30, 2017 2:53 PMIf the goal is to terminate all processing, why not just exit the procedure?
IF (SELECT COUNT(*) FROM #TmpErrorLog = 0)
BEGIN
RETURN;
END;SELECT stuff...
Maybe I'm old-school, but don't test for a condition that you're not going to do anything with.IF (SELECT COUNT(*) FROM #TmpErrorLog > 0)
/* One simple change to the condition allows you to leave out the messy RETURN; */
BEGIN
SELECT stuff...
END;
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 30, 2017 at 9:25 pm
drew.allen - Tuesday, May 30, 2017 7:48 PMEd Wagner - Tuesday, May 30, 2017 2:53 PMIf the goal is to terminate all processing, why not just exit the procedure?
IF (SELECT COUNT(*) FROM #TmpErrorLog = 0)
BEGIN
RETURN;
END;SELECT stuff...
Maybe I'm old-school, but don't test for a condition that you're not going to do anything with.
IF (SELECT COUNT(*) FROM #TmpErrorLog > 0)
/* One simple change to the condition allows you to leave out the messy RETURN; */
BEGIN
SELECT stuff...
END;
That'll work too.
June 2, 2017 at 4:22 am
Appreciate the help guys!!
Ok, I've put this together - bear in mind I'm NO Developer, this is the most SQL I've written in my DBA career, so the procedure and choice of commands may not be the most efficient - and if anyone knows where I can get some junk data to use for testing (I do NOT wanna be one of those guys I complain vociferously about , who test their code on 1 row, not 1 million!) that'd be cool too 🙂
--------------------POll SQL Server Logs For Errors Every X Seconds-------------------------
--Created By JaybeeSQL, 2/6/2017--
---------------------------------------------------------------------------- --Suppress informational messages
SET NOCOUNT On
USE TempDB;
GO
--Create Holding Table For Errors, If Non-Existant;
IF NOT EXISTS
(SELECT *
FROM TempDB.sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[#TmpErrorLog]')
AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[#TmpErrorLog]
(
[LogDate] DATETIME NULL
,[ProcessInfo] VARCHAR(20) NULL
,[Text] VARCHAR(MAX) NULL
,[Flag] DATETIME NULL
)
Print 'System recently restarted, therefore error messages may be duplicates'
End
-- Populate Temporary Error Table With Error Log Data
INSERT INTO #TmpErrorLog
(
[LogDate]
,[ProcessInfo]
,[Text]
)
-- Test Data EXEC master.dbo.xp_readerrorlog 0 , 1
EXEC master.dbo.xp_readerrorlog 0 , 1, ", Severity: 16, State:"
EXEC master.dbo.xp_readerrorlog 0 , 1, ", Severity: 17, State:"
EXEC master.dbo.xp_readerrorlog 0 , 1, ", Severity: 18, State:"
EXEC master.dbo.xp_readerrorlog 0 , 1, ", Severity: 19, State:"
EXEC master.dbo.xp_readerrorlog 0 , 1, ", Severity: 20, State:"
EXEC master.dbo.xp_readerrorlog 0 , 1, ", Severity: 21, State:"
EXEC master.dbo.xp_readerrorlog 0 , 1, ", Severity: 22, State:"
EXEC master.dbo.xp_readerrorlog 0 , 1, ", Severity: 23, State:"
EXEC master.dbo.xp_readerrorlog 0 , 1, ", Severity: 24, State:"
EXEC master.dbo.xp_readerrorlog 0 , 1, ", Severity: 25, State:"
-- Check Whether any (Sufficiently Severe) Unnotified Errors Exist, and if so, return these
If (Select COUNT(*)
From #TmpErrorLog) > 0
Begin
Select *
From #TmpErrorLog
Where Flag is null
End
--Set Flag to '1' to Denote existing/notified errors
Update #TmpErrorLog
Set Flag = 1
Where Flag is null--Table should be periodically wiped if over
--
--
--
;
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply