Search results matching tag 'SQL output target'

Why is the SQL output target so slow?

PWills — Sun, 09 Aug 2009 03:34:08 GMT

Frustrated with the speed of the SQL output directives, I just ran a little experiment. I used -o:sql to dump a log query on a 10MB IIS file. It took 2:30 (I'm using the COM wrapper). I then used the "interactive mode" in COM, assembled a System.Data.DataTable by hand, and then used System.Data.SqlClient.SqlBulkCopy to bulk insert to the database. It took 18ms. I don't understand why LogParser's -o:sql output directive is over 8000x slower than my crappy code. Am I doing something wrong, or is this a known issue? I'm using SQL Server 2008, if that makes any difference.

Inserting higher resolution dates into SQL Server 2008 datetime2 type

MikeGale — Thu, 14 May 2009 06:35:12 GMT

I have some time data in logs with a sub-second time resolution.

I can get this safely into log parser using the iTsFormat setting.

When I put that into SQL Server 2008 I have (so far) gotten the times in seconds (loss of resolution). The target field is datetime2 (not datetime).

How do I preserve the resolution when inserting the data?

Re: SQL Server Connection String That Works

FHM786 — Wed, 11 Mar 2009 08:30:02 GMT

C:\Program Files\Log Parser 2.2>logparser -i:w3c "SELECT TO_LOCALTIME(TO_Timesta
mp(REPLACE_STR(STRCAT(STRCAT(date,' '), time),' GMT',''),'yyyy-M-d h:m:s')) as D
ateTime, [Server-hostname], [Recipient-Address],[Event-ID], [total-bytes], [Numb
er-Recipients], TO_LOCALTIME(TO_Timestamp(REPLACE_STR([Origination-time], ' GMT'
,''),'yyyy-M-d h:m:s')) as [Origination Time], [Message-Subject], [Sender-Addres
s] INTO EmailLogs FROM 'C:\20081006.log' where ([Event-ID] = '1033') or ([Event-
ID] = '1020')" -o:SQL -oConnString:"Driver={SQL Native Client};Server="dursql02"
; Database="MscDW"; Trusted_Connection=yes;username=XXXXX;password=XXXXX" -cre
ateTable:ON
Task aborted.
Error connecting to ODBC Server
SQL State:     08001
Native Error: 53
Error Message: [Microsoft][SQL Native Client]Named Pipes Provider: Could
not open a connection to SQL Server [53].

Statistics:
-----------
Elements processed: 0
Elements output:    0
Execution time:     16.17 seconds

________________________________________________________________________

C:\Program Files\Log Parser 2.2>logparser -i:w3c "SELECT TO_LOCALTIME(TO_Timesta
mp(REPLACE_STR(STRCAT(STRCAT(date,' '), time),' GMT',''),'yyyy-M-d h:m:s')) as D
ateTime, [Server-hostname], [Recipient-Address],[Event-ID], [total-bytes], [Numb
er-Recipients], TO_LOCALTIME(TO_Timestamp(REPLACE_STR([Origination-time], ' GMT'
,''),'yyyy-M-d h:m:s')) as [Origination Time], [Message-Subject], [Sender-Addres
s] INTO EmailLogs FROM 'C:\20081006.log' where ([Event-ID] = '1033') or ([Event-
ID] = '1020')" -o:SQL -oConnString:"Driver={SQL Native Client};Server=dursql02;
Database=MscDW; Trusted_Connection=yes;username=XXXXX;password=XXXXX" -createT
able:ON
Task aborted.
An error occurred while uploading data to SQL table
SQL State:     42000
Native Error: 7202
Error Message: [Microsoft][SQL Native Client][SQL Server]Could not find
server 'DURSQL02' in sys.servers. Verify that the correct server name was
specified. If necessary, execute the stored procedure sp_addlinkedserver to
add the server to sys.servers.

Statistics:
-----------
Elements processed: 426
Elements output:    80
Execution time:     0.34 seconds

C:\Program Files\Log Parser 2.2>

Please help

CharIndex unavailable?

jcAgora — Mon, 28 Jul 2008 19:33:31 GMT

Hi there, I tried to parse the info in "cs-uri-stem",

In "cs-uri-stem" it contain a string like " /Demodir/_233_demo.gif"

I just want to parsed the "233" . So I used CharIndex.

I tested in CharIndex on SQL and it worked, but once I run it in LogParser it failed. The error message is as following:

Error: Syntax Error: <field-expr>: cannot find a vaild <field-expre>:'CHARINDEX('_',')

This is my query:

LOGPARSER -i:IISW3C "Select LogRow, time, c-ip, cs-method,cs-uri-stem,SUBSTR(cs-uri-stem, CHARINDEX('_', cs-uri-stem) + 1, (CHARINDEX('_', cs-uri-stem,CHARINDEX('_', cs-uri-stem) + 1) - CHARINDEX('_', cs-uri-stem) - 1)) as ContentId
from C:\WINDOWS\system32\Logfiles\W3SVC1\ex080725.log where cs-uri-stem Like '/DemoWeb/Images/Up/EML/%'" -o:datagrid

any suggestion is welcome.

Thanks in advanced,

---Joe

Connecting to Oracle XE from Log Parser 2.0

NathanBlakley — Fri, 20 Jun 2008 22:13:22 GMT

Hi, I am having trouble connecting to Oracle XE to import IIS logs. Each time that I try a variation on (some names changed to protect the innocent):

LogParser.exe -i:IISW3C file:iislogquery.sql?logpath="V:\\Logfiles\\XYZ" -o:SQL -oConnString:"Driver={Oracle in Home_Default10g};DBQ=111.111.111.111:1521/XE;Uid=mydw;Pwd=mypswd"

The following error is presented:

Task aborted.
Error connecting to ODBC Server
SQL State: IM002
Native Error: 0
Error Message: [Microsoft][ODBC Driver Manager] Data source name not found
and no default driver specified

Attempts to sent to a CSV have been successful. The switch to SQL seems to break. Any ideas of what I am doing wrong?
Many thanks.

Nathan

Output SQL Error Log to SQL Database Table

modemgeek — Mon, 19 May 2008 19:17:34 GMT

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

SQL Output : Why "Select * [...]" before inserting ? --> Memory pb with big database

Hoplebus — Sat, 02 Feb 2008 18:27:56 GMT

Hi,

I'm a long time LP user and I'm faced with a strange behaviour when outputing to SQL.

I'm using LP v2.2, Mysql v5.1 and MySQL ODBC Connector v3.51.

I'm uploading parsed results to a MySQL DB.

The destination table already contains ~1GB of data.

When LP finishes parsing and just before uploading recordsets to the DB, it looks like it first issues a "Select * from OutputTable"

--> memory consuption explodes and LP exists (in fact the error message comes from the ODBC connector) with an "out of memory error".

I diagnosed the "Select *" query by setting up "Tracing" in Windows ODBC administrative pannel and analysing the SQL.log file. I too can see this query on the server side.

The params of the SQL output are :

createTable : off

clearTable : off

I tried to play around with the other SQL output params without success.

I see no obvious need to launch such a query before issuing the Inserts.

I have 2 workarounds that consists in :

- getting the recordsets from LP and issuing myself the Inserts using ADO, but as I'm using VBScripts, I imagine that performance will be very poor and my script will have to keep the parsed result in memory before uploading it to the DB.

- output a TSV file (tab separator) and issue a "LOAD DATA INFILE" to bulk load it into Mysql, but this is more complex and not portable at all...

In fact, I don't know if it's LP who is explicitly issuing the "Select *" query or if it is generated by the Mysql Connector.

Could anyone "debug", say, LP inserting into SQL Server to see if it exhibits the same behaviour ?

Has anybody an idea on how to solve that issue ?

Thanks in advance !

Re: Auto-increment primary key in SQL tables

gian.laurenzi — Thu, 01 Nov 2007 17:08:57 GMT

Thank you, but this is another problem related to create a new SQL table!

Please somebody help me, how can i create a SQL table with an auto-increment (IDENTITY(1,1)) primary key, a datetime field and others text fields?

I have some logs (from router/firewall) with this format:

SourceAddress ; SendTime ; Facility ; Severity ; MessageText

192.168.1.1; 01/11/2007 18.00.00; local7; Alert; Some text...

I want to create a SQL table (I'm using SQL Server 2005 Express Edition) using this query:

SELECT 1, TO_TIMESTAMP (SendTime, 'dd/MM/YYYY?hh.mm.ss' ) AS Send_Time, Facility, Severity, MessageText
INTO [ IPADDRESS ]
FROM 'LogFile_*.csv'
WHERE SourceAddress = 'IPADDRESS' (note that IPADDRESS is a variable)

quite easy, isn't?

But I encountered several problems:

I am unable to create an auto-increment primary key.

I am unable to create a datetime field with the function TO_TIMESTAMP (note that i use italian date format), without the TO_TIMESTAMP it creates a String field.

Please help me!!

Thanks in advance!

Auto-increment primary key in SQL tables

gian.laurenzi — Mon, 22 Oct 2007 11:07:20 GMT

Hello, I'm trying to create and populate some SQL Tables from log file using LogParser COM API.

I use SQL Output format with -createTable option enabled and i need an auto-generated identity column (an auto-increment primary key!)

I tried

SELECT 1, SendTime, Facility, Severity, MessageText ;
        INTO [" + IPAdd.ToString() + "] "
        FROM '" logfile + "' ";
        WHERE SourceAddress LIKE ' " + IPAdd.ToString() + " '"

but I obtained

Error executing query: Error creating table
SQL State: 42000
Native Error: 102
Error Message: [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near 1.
Most likely you're attempting to create a table with invalid column names, such as names containing the '-' character, or names that are illegal for the database you are connecting to (e.g. 'timestamp'). Rename the columns in the SELECT clause using the AS statement. [Unknown Error]

OK! so I change my query in

SELECT 1 AS LogID, SendTime, Facility, Severity, MessageText ;
        INTO [" + IPAdd.ToString() + "] "
        FROM '" logfile + "' ";
        WHERE SourceAddress LIKE ' " + IPAdd.ToString() + " '"

but I always obtain 1 in the first column, when i needed an identity column!

As you can see in my other post, i tried to create tables at run-time with SQLCommands embedded in my C# code, but it generates another error!

Please help me!

Thanks in advance,

Giancarlo Laurenzi.

SQLEXPRESS

LogParser User : IanB — Thu, 21 Jun 2007 17:54:00 GMT

All,

I am trying to import a merged W3C Extended log into a SQLEXPRESS instance.

My LogParser 2.2 query is as follows:

LogParser -i:W3C -o:SQL "SELECT date, time, s-computername, c-ip, cs-uri-stem, cs-uri-query, sc-status, sc-substatus, sc-win32-status, time-taken, sc-bytes, cs-bytes FROM MERGED_LOG.log TO W3CLogs" -driver:"SQL Server" -server:.\sqlexpress -database:IISLogs -createtable: ON -fixColNames: ON

I get the following error:

Task aborted.
An error occurred while uploading data to SQL table
SQL State: 42000
Native Error: 7202
Error Message: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not
find server 'IANBE-LT-M5\SQLEXPRESS' in sys.servers. Verify that the
correct server name was specified. If necessary, execute the stored
procedure sp_addlinkedserver to add the server to sys.servers.

Statistics:
-----------
Elements processed: 81
Elements output: 80
Execution time: 0.83 seconds

I see that the table is created in the database but is never populated.

Any help you can offer would be greatly appreciated