How to combine multiple EXEC statements into a single SELECT?

Question

I run this every month to extract database logs, and I have to change the dates as well.

DECLARE @start DATETIME
SET @start = CONVERT(DATETIME, '2020-08-01 00:00');
DECLARE @end DATETIME
SET @end   = CONVERT(DATETIME, '2020-08-31 23:59');
DECLARE @searchString1 NVARCHAR(256) = 'BACKUP';
DECLARE @searchString2 NVARCHAR(256) = '';
EXEC xp_readerrorlog 0, 1, @searchString1, @searchString2, @start, @end;
EXEC xp_readerrorlog 1, 1, @searchString1, @searchString2, @start, @end;
EXEC xp_readerrorlog 2, 1, @searchString1, @searchString2, @start, @end;
EXEC xp_readerrorlog 3, 1, @searchString1, @searchString2, @start, @end;
EXEC xp_readerrorlog 4, 1, @searchString1, @searchString2, @start, @end;
EXEC xp_readerrorlog 5, 1, @searchString1, @searchString2, @start, @end;

However, I get multiple results that I have to manually sift through and combine

How can I do something like this:

SELECT * FROM (EXEC xp_readerrorlog 0, 1, @searchString1, @searchString2, @start, @end)
UNION ALL
SELECT * FROM (EXEC xp_readerrorlog 1, 1, @searchString1, @searchString2, @start, @end)
UNION ALL
etc

Obviously, this does not work. I get

Incorrect syntax near the keyword 'EXEC'.

Is there a better way?

score 2 · Answer 1 · answered Sep 23 '20 at 08:34

2

I don't think you can combine several logs in one EXEC, so you are most likely stuck with several executions. As suggested nu Mo64, you can put it all in a temp table and then query that temp table. I have a utility that does exactly that, here.

answered Sep 23 '20 at 08:34

Tibor Karaszi

18,269
2
17
30

score 1 · Answer 2 · answered Sep 23 '20 at 08:18

For the dates use dateadd to go back a specific number of days from todays date (getdate()) You can use xp_enumerrorlogs to get the metadata about how many log files there are and then write a loop to iterate through them storing the results in a temp table.

as explained here :-

https://sqlmatters.com/Articles/Searching-ALL-SQL-Server-Logs-using-TSQL.aspx

How to combine multiple EXEC statements into a single SELECT?

2 Answers2