« Previous Next »

• 05-19-2008, 3:17 PM

  modemgeek Joined on 05-19-2008, 3:11 PM Posts 9

  Output SQL Error Log to SQL Database Table Reply Contact I'm having problems exporting the log to a SQL database.  The errors I seem to get are related to the field name/data type.  I ran the following below to get the field names. LogParser "select * INTO C:\log.txt From 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'" -i:TSV -o:TSV -headerrow:off This is what it returns: Filename RowNumber Field1 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG 1 2008-05-11 22:58:21.48 Server      Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG 2 Oct 14 2005 00:33:37 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG 3 Copyright (c) 1988-2005 Microsoft Corporation It appears that the only fields are filename, rownumber, and field1.  How do I address the date field, source, and message field? I can output to a text file with no problem, but with into SQL server I keep getting errors.  If I use the iHeaderFile parameter, what do I put in the text file?  This is what I have so far. LogParser "select STRCAT(STRCAT(DATE,' '), TIME) as LogDateTime, SOURCE, Message INTO SQLEvents2 From 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'" -i:TSV -iseparator:spaces -iHeaderFile:c:\scripts\sqleventsformatfile.txt -headerRow:OFF -nskiplines:9 -o:SQL -server:xxxx -database:Events -driver:"SQL Server" -username:xxxx -password:xxxx -createTable:ON

  Tags: SQL output target

• 05-19-2008, 5:31 PM

  In reply to

  modemgeek Joined on 05-19-2008, 3:11 PM Posts 9

  Re: Output SQL Error Log to SQL Database Table Reply Contact I was able to make some progress with this. LogParser "SELECT Field1 AS Date, Field2 AS Time, Field3 AS Source, Field4 AS Message INTO SQLEvents FROM 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'" -i:TSV -iseparator:spaces -headerRow:OFF -nFields:4 -nskiplines:9 -o:SQL -server:xxxx -database:xxxx -driver:"SQL  Server" -username:xxxx -password:xxxx -createTable:ON I got all the data formatted properly but when I add the                                                  -iCheckpoint:SQLEventsCheckpoint.lpc at the end, the data end ups being blank.  However w/o it, it looks fine: 2008-05-11 22:58:21.48 Server This instance of SQL Server last reported using a process ID of 1780 at 5/11/2008 10:54:29 PM (local) 5/12/2008 5:54:29 AM (UTC). This is an informational message only; no user action is required. Does anyone know why using -iCheckpoint:SQLEventsCheckpoint.lpc  would create all blank entries?  It adds exactly 100 blank records, in which there are 100 records.

• 06-12-2008, 10:59 AM

  In reply to

  swhtng Joined on 06-12-2008, 2:56 PM Posts 1	Re: Output SQL Error Log to SQL Database Table Reply Contact  Hi,  SQL2005 ERRORLOG is in Unicode format, you should add the codepage parameter to the query: -iCodepage:-1 I don't know the exact reason of the issue, but this resolves it Erik.

• 06-16-2008, 6:31 PM

  In reply to

  modemgeek Joined on 05-19-2008, 3:11 PM Posts 9	Re: Output SQL Error Log to SQL Database Table Reply Contact Thanks.  I found a post where someone mentioned that and it did work.  I forgot all about my post and should have updated it.