The insights of an SQL geek: 02/2009

Monday, February 16, 2009

How to import the error log contents into a specified table?

Hello all.
try this Tsql to import your error log into a table.
usage :
EXEC sp_import_errorlog 'myerrorlog'



CREATE PROC sp_import_errorlog
(
@log_name sysname,
@log_number int = 0,
@overwrite bit = 0
)
AS

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