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

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<br/>EXEC sp_import_errorlog &#39;myerrorlog&#39;<br/><br/>To import the current error log to table myerrorlog, and overwrite the table &#39;myerrorlog&#39; if it already exists<br/>EXEC sp_import_errorlog &#39;myerrorlog&#39;, @overwrite = 1<br/><br/>To import the previous error log to table myerrorlog<br/>EXEC sp_import_errorlog &#39;myerrorlog&#39;, 1<br/><br/>To import the second previous error log to table myerrorlog<br/>EXEC sp_import_errorlog &#39;myerrorlog&#39;, 2

Views 785 Downloads 298

'errorlog', 'log', 'error', 'sql', 'import', 'convert', 'read', 'table', 'stored procedure', 'stored', 'procedure', 'mysql', 'NotMyWork', 'Database', 'maintenance'

ANmarAmdeen
333
Database SQL
Revisions

v1.0