• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 583
  • Last Modified:

SQL Server Script to Import ErrorLog

Not very good with script.  
Can somebody modify the following script, so it can be executed in SQL Server 2008 and R2 without returning error. The was tested on a 2000 Instance of SQL but never tested on 2K5 or 2K8.

CREATE PROC sp_import_errorlog
      @log_name sysname,
      @log_number int = 0,
      @overwrite bit = 0
Purpose:      To import the SQL Server error log into a table, so that it can be queried

Tested on:       SQL Server 2000

Limitation:       With error messages spanning more than one line only the first line is included in the table

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


      DECLARE @sql varchar(500) --Holds to SQL needed to create columns from error log

      IF (SELECT OBJECT_ID(@log_name,'U')) IS NOT NULL
                  IF @overwrite = 0
                              RAISERROR('Table already exists. Specify another name or pass 1 to @overwrite parameter',18,1)
                              RETURN -1
                              EXEC('DROP TABLE ' + @log_name)

      --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
      DELETE #errlog

      SET @sql =       'SELECT
                        CONVERT(DATETIME,LEFT(err,23)) [Date],
                        SUBSTRING(err,24,10) [spid],
                        RIGHT(err,LEN(err) - 33) [Message],
                  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
1 Solution
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please try to use xp_readerrorlog instead of sp_readerrorlog
FavorableAuthor Commented:

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now