SQL Server Script to Import ErrorLog

Posted on 2011-05-05
Last Modified: 2012-05-11
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
Question by:Favorable
    LVL 142

    Accepted Solution

    please try to use xp_readerrorlog instead of sp_readerrorlog

    Author Closing Comment


    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now