In SQL Server 2012, xp_readerrorlog has undergone a minor change. if you use xp_readerrorlog as used before you may encounter the following error
for ex: -
EXEC master.dbo.Xp_readerrorlog
0,
1,
'',
'',
'20130502 00:00',
'20130503'
throws a error saying
Msg 22004, Level 12, State 1, Line 0
Error executing extended stored procedure: Invalid Parameter Type
If you are like me, who likes to capture the results of errorlog into a table then you may get a different error.
for ex:
INSERT INTO #read_error_log
EXEC master.dbo.Xp_readerrorlog
0,
1,
'',
'',
'20130502 00:00',
'20130503'
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
The reason for the error is that the third and the fourth parameter, to be passed as a search strings strictly accepts nvarchar Datatype. Adding the letter 'N'; makes the parameter into nvarchar as shown below .
EXEC master.dbo.Xp_readerrorlog
0,
1,
N'',
N'',
'20130502 00:00',
'20130503'
The script provided below will help one read the error log quickly and effectively. The script is similar to the one shared in this blog previously over here. Instead of using sp_readerrorlog, the script below uses xp_readerrorlog with additional parameters which makes it even more effective. sp_readerrorlog doesn't provide time based filters (6th & 7th parameters) which are the most important filters while reading errorlog.
CREATE TABLE #read_error_log
(
logdate DATETIME,
processinfo VARCHAR(200),
errorlogtext VARCHAR(max)
)
INSERT INTO #read_error_log
EXEC master.dbo.Xp_readerrorlog
0,
1,
N'',
N'',
'20130502 00:00',
'20130503'
SELECT *
FROM #read_error_log
ORDER BY logdate DESC
TRUNCATE TABLE #read_error_log
DROP TABLE #read_error_log
