Friday, December 31, 2010

Steps to insert error log records into temporary table

You can follow the below steps to enter error log records into temporary table and query it.








-- Command will create the temporary table in tempdb
CREATE TABLE [dbo].[#TmpErrorLog]
([LogDate] DATETIME NULL,
 [ProcessInfo] VARCHAR(20) NULL,
 [Text] VARCHAR(MAX) NULL ) ;

-- Command will insert the errorlog data into temporary table
INSERT INTO #TmpErrorLog ([LogDate], [ProcessInfo], [Text])
EXEC [master].[dbo].[xp_readerrorlog] 0 ;

-- retrieves the data from temporary table
SELECT * FROM #TmpErrorLog


Posted by at
Labels: , , ,

2 comments:

  1. Abhinav AndhareJanuary 5, 2011 at 3:35 AM

    Jugal, looks Awesome and we can use this on the mission critical situations to dig more from error logs...
    I like to add here

    run the xp_enumerrorlogs stored procedure and get the number of file numbers and insert all the data to the temp table to be more priciese...

    Thanks again for uploading this article...

    Good Learning this

    Abhinav

    ReplyDelete
  2. capgeminiMay 6, 2011 at 2:15 PM

    You can use this too...

    Begin
    /*declaring sql variable */
    declare
    @sql varchar(1000)
    set
    @sql=''
    /* Creating a temp table */
    create
    table #errors
    (
    rowid
    int identity,
    entrytime
    datetime,
    source
    varchar(50),
    logentry
    varchar(4000)
    )
    /* assigning errorlog to @sql variable */
    select
    @sql= 'EXEC master..xp_readerrorlog'
    /* inserting errorlog from variable into table */
    insert
    into #errors (entrytime,source,logentry)
    execute
    (@sql)
    select
    * from #errors
    --drop table #errors
    end

    ReplyDelete

Subscribe to: Post Comments (Atom)