March 26, 2012 at 1:27 pm
Hi, is it possible to make a query to show the row that matches the where criteria and also the row above or below it? I ask because I would like to run a TSQL query of a table that holds the SQL logs from all of the servers in the environment. I have a job that copys the the error logs into a table on my management server. It comes from this article and is working great.
Problem is that errors are written in 2 rows. The row with "Error:...." and the above row that actually states what the error is. Is there something I can do with ROW_NUMBER() or another method to query for errors and include the description row as well?
Columns are Server,LogDate,ProcessInfo,Text
Thanks for reading.
March 26, 2012 at 1:43 pm
Theory first:
In a table, there is no natural "above" or "below". It all depends on the ORDER BY criteria.
Ok, end of theory. Unfortunately, the wErrorLog table in the link you mentioned, doesn't have any primary key.
But you could try to use something like this:
;
WITH cte AS
(
SELECT ErrorId
FROM wErrorLog
WHERE <your criteria>
)
SELECT *
FROM cte
OUTER APPLY
(
SELECT TOP 1 <col1>
FROM wErrorLog e
WHERE e.ErrorId < cte.ErrorId
ORDER BY e.ErrorId DESC
) beforeCurrent
OUTER APPLY
(
SELECT TOP 1 <col1>
FROM wErrorLog e
WHERE e.ErrorId > cte.ErrorId
ORDER BY e.ErrorId
) afterCurrent
March 26, 2012 at 2:01 pm
Thanks for the reply and explanation. I'm sure that your example works in the wErrorlog table that is created on each server prior to writing to the table on the management server.
At this point I'm working from a later step where I have all error logs written to the managment server in a large table called SQL_Errorlogs. This table is where the query will run.
March 26, 2012 at 2:05 pm
Without knowing the table structure it's hard to tell what needs to be changee in the code snippet I provided.
I'm sure it can be modified to meet your criteria.
March 26, 2012 at 2:10 pm
Table structure matches the error log with the additional of a server name column.
Server,LogDate,ProcessInfo,Text
I've already made several attempts to modify your example using ROW_NUMBER to assign a number to each row without success but I'll keep working on it.
March 26, 2012 at 2:18 pm
Given the table structure you posted there's no way to find the rows that should match.
Let's assume there are four rows, all having the same values for Server,LogDate,ProcessInfo.
How would you sort those rows and define "before" and "after"?
March 26, 2012 at 4:40 pm
Thanks Lutz,
Using your OUTER APPLY example, I put together something that works. The extra rows all have the same datetime stamp so I can find them by querying the SQL_Errorlog table. Occasionally it outputs an additional row if it has the same time stamp but it works well enough.
Thanks again
;
WITH cte AS
(
SELECT *
FROM sql_errorlog
WHERE text like '%error:%' and datediff(dd,logdate,getdate()) < 2
)
SELECT beforeCurrent.*
FROM cte
OUTER APPLY
(
SELECT *
FROM sql_errorlog e
WHERE e.server = cte.server and e.logdate = cte.LogDate
) beforeCurrent
where beforecurrent.text not like '%error:%'
order by server,LogDate desc
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply