• 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
)
AS
/*************************************************************************************************
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

*************************************************************************************************/

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
      
0
Favorable
Asked:
Favorable
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please try to use xp_readerrorlog instead of sp_readerrorlog
0
 
FavorableAuthor Commented:
Thanks
0

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