Import error log

Imports the SQL Server error log into a table, so that it can be queried
This is a "Stored Procedure" that can be saved in SQL Server and called when needed
By: Narayana Vyas Kondreddi, http://vyaskn.tripod.com, vyaskn@hotmail.com
Limitation: With error messages spanning more than one line only the first line is included in the table

CodeFunctionName
What is this?

Public

Tested

Imported
CREATE PROC sp_import_errorlog (
@log_name sysname,
@log_number int = 0,
@overwrite bit = 0
) AS
/*************************************************************************************************
Purpose: To import the SQL Server error log into a table, so that it can be queried
Written by: Narayana Vyas Kondreddi http://vyaskn.tripod.com
Tested on: SQL Server 2000
Limitation: With error messages spanning more than one line only the first line is included in the table
Date modified: April-16-2001 1:05 PM
Email: vyaskn@hotmail.com
Example 1: To import the current error log to table myerrorlog
EXEC sp_import_errorlog 'myerrorlog'
Example 2: To import the current error log to table myerrorlog, and overwrite the table
'myerrorlog' if it already exists
EXEC sp_import_errorlog 'myerrorlog', @overwrite = 1
Example 3: To import the previous error log to table myerrorlog
EXEC sp_import_errorlog 'myerrorlog', 1
Example 4: To import the second previous error log to table myerrorlog
EXEC sp_import_errorlog 'myerrorlog', 2
*************************************************************************************************/
BEGIN
SET NOCOUNT ON
DECLARE @sql varchar(500) --Holds to SQL needed to create columns from error log
IF (SELECT OBJECT_ID(@log_name,'U')) IS NOT NULL
BEGIN
IF @overwrite = 0
BEGIN
RAISERROR('Table already exists. Specify another name or pass 1 to @overwrite parameter',18,1)
RETURN -1
END
ELSE
BEGIN
EXEC('DROP TABLE ' + @log_name)
END
END
--Temp table to hold the output of sp_readerrorlog
CREATE TABLE #errlog
(
err varchar(1000),
controw tinyint
)
--Populating the temp table using sp_readerrorlog
INSERT #errlog
EXEC sp_readerrorlog @log_number
--This will remove the header from the errolog
SET ROWCOUNT 4
DELETE #errlog
SET ROWCOUNT 0
SET @sql = 'SELECT
CONVERT(DATETIME,LEFT(err,23)) [Date],
SUBSTRING(err,24,10) [spid],
RIGHT(err,LEN(err) - 33) [Message],
controw
INTO ' + QUOTENAME(@log_name) +
' FROM #errlog ' +
'WHERE controw = 0'
--Creates the table with the columns Date, spid, message and controw
EXEC (@sql)
--Dropping the temporary table
DROP TABLE #errlog
SET NOCOUNT OFF
PRINT 'Error log successfully imported to table: ' + @log_name
END

To import the current error log to table myerrorlog
EXEC sp_import_errorlog 'myerrorlog'

To import the current error log to table myerrorlog, and overwrite the table 'myerrorlog' if it already exists
EXEC sp_import_errorlog 'myerrorlog', @overwrite = 1

To import the previous error log to table myerrorlog
EXEC sp_import_errorlog 'myerrorlog', 1

To import the second previous error log to table myerrorlog
EXEC sp_import_errorlog 'myerrorlog', 2

Views 4,093

Downloads 1,390

CodeID
DB ID