?
Solved

Log Parser 2.2: data type is not compatible with SELECT clause

Posted on 2008-01-30
6
Medium Priority
?
2,327 Views
Last Modified: 2013-11-18
Hi all,

I have a problem with Log Parser 2.2 and CSV files I hope you can help me with. When run log parser against my CSV files, I get the following error:

Task aborted.
SQL table column "Originator" data type is not compatible with SELECT clause item "ORIGINATOR" (type INTEGER)

Now the SQL table column called "Originator" is set up as a varchar, because the CSV column called "ORIGINATOR" could contain either numbers or letters (though most often numbers). Log parser thinks that this column is an integer because the first log it analyses only contains numbers, but subsequent logs will contain both numbers and letters.

Therefore, if I change my SQL column data type to integer, subsequent logs will fail saying the data type is wrong again!

Surely there must be a way with Log Parser to tell it to simply take the CSV columns and ignore their data type and just insert their values into the SQL table?

Thanks

Jon
0
Comment
Question by:Jon Winterburn
  • 3
  • 2
6 Comments
 
LVL 51

Accepted Solution

by:
Ted Bouskill earned 2000 total points
ID: 20783922
LogParser uses the Microsoft Text driver to parse the files.  You can control the behavior of the driver using a schema.ini in the same folder as the file source (the log folder)

http://msdn2.microsoft.com/en-us/library/ms709353.aspx
0
 
LVL 11

Author Comment

by:Jon Winterburn
ID: 20785653
Thanks very much for this link and for your help. I've tried to use the Schema.ini but it doesn't seem to work. I'm probably missing something really basic, so please tell me if I am.

I get the error "Error: SELECT clause: Syntax Error: unknown field 'ORIG'"

What I have is this:

1. The CSV file has no headers for the columns
2. My Schema.ini is in the logs folder
3. My batch file that executes log parser is in the folder above the logs folder

My Schema.ini is as follows:
Format=CSVDelimited
Col1=NOTREQ1
Col2=ORIG
Col3=DEST
Col4=DESTCONTEXT
Col5=CLID
Col6=CHANNEL
Col7=DESTCHANNEL
Col8=LASTAPP
Col9=LASTAPPDATA
Col10=STARTOFCALL
Col11=ANSWERTIME
Col12=ENDTIME
Col13=DURATION
Col14=NOTREQ2
Col15=BILLSECONDS
Col16=DISPOSITION
Col17=NOTREQ3

My batch file is in the code snippet below.
cd "C:\Program Files\log parser 2.2\"
LogParser -i:CSV -headerRow:off "SELECT 1,ORIG,DEST,DESTCONTEXT,CLID,STARTOFCALL,ANSWERTIME,ENDTIME,DURATION,BILLSECONDS,DISPOSITION INTO Call_Logs FROM D:\LogFiles\Asterisk\Call_Logs\*.csv" -o:SQL -server:mymssql -driver:"SQL Server" -database:AsteriskData -username:myuser -password:mypassword -createtable:ON

Open in new window

0
 
LVL 51

Expert Comment

by:Ted Bouskill
ID: 20790210
I think you have to be in the current folder when you run the EXE
cd "D:\LogFiles\Asterisk\Call_Logs\"
C:\Program Files\log parser 2.2\LogParser -i:CSV -headerRow:off "SELECT 1,ORIG,DEST,DESTCONTEXT,CLID,STARTOFCALL,ANSWERTIME,ENDTIME,DURATION,BILLSECONDS,DISPOSITION INTO Call_Logs FROM *.csv" -o:SQL -server:mymssql -driver:"SQL Server" -database:AsteriskData -username:myuser -password:mypassword -createtable:ON
 

Open in new window

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 50

Expert Comment

by:Lowfatspread
ID: 20792135
could it be that you haven't named the first table column ?

"SELECT 1 as [id],ORIG,DEST   ...

do you  want to maje that

"SELECT "1" as [id],ORIG,DEST,...

it would be better practice to  explicitly create the permanent table call_logs prior to running the statement (or truncate it prior to each run)

and then use Insert into (column list ) select column list ....

   
0
 
LVL 11

Author Comment

by:Jon Winterburn
ID: 20795877
Tedbilly - I have amended it as you said, and even put everything (schema.ini and .bat file) into the same folder as the logs, but still I get the error "SELECT clause: Syntax Error: unknown field 'ORIG'".

I get the same if I do the "as [id]" - this is not necessary anyway, as by entering "select 1," tells the log parser to tell the unique ID column to increment by one (I have implemented this in other log parser scripts which do work).

So now my script is as below in the snippet.
cd "D:\LogFiles\Asterisk\Call_Logs\"
"C:\Program Files\log parser 2.2\LogParser" -i:CSV -headerRow:off "SELECT 1,ORIG,DEST,DESTCONTEXT,CLID,STARTOFCALL,ANSWERTIME,ENDTIME,DURATION,BILLSECONDS,DISPOSITION INTO Call_Logs FROM *.csv" -o:SQL -server:mymssql -driver:"SQL Server" -database:AsteriskData -username:myuser -password:mypass -createtable:ON

Open in new window

0
 
LVL 11

Author Comment

by:Jon Winterburn
ID: 20813277
Well, I got fed up of messing around with this and decided it was easier to simply spend 10 minutes adding the column names to all the CSV files. Once I did this, I could then run log parser and it all worked fine. Thanks for all your help.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
Suggested Courses

588 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