0

I'm using logparser to read information from IIS into SQL.

& "C:\Program Files (x86)\Log Parser 2.2\LogParser.exe" "select EXTRACT_PREFIX(cs-uri-stem,2,'/') as Site, cs-username as Anvandarnamn, date as Datum INTO Raw_Statestik from C:\Statestik\Test\*.log where cs-username like '%%Domain%%' and cs-uri-stem like '/sites/%%' group by EXTRACT_PREFIX(cs-uri-stem,2,'/'), Anvandarnamn, Datum ORDER BY Site, Anvandarnamn ,Datum DESC" -recurse:1 -i:IISW3C -o:sql -server:Test\SQL2 -database:Anvandarstatestik -cleartable:OFF -transactionRowCount:-1

When I do this, I get the following error:

Task aborted. LogParser.exe : SQL table column "Datum" data type is not compatible with SELECT clause item At line:5 char:1

  • & "C:\Program Files (x86)\Log Parser 2.2\LogParser.exe" "select EXTRA ...
  •   + CategoryInfo          : NotSpecified: (SQL table colum...ECT clause item:String) [], RemoteException
  + FullyQualifiedErrorId : NativeCommandError

"Datum" (type TIMESTAMP)

Statistics:

Elements processed: 0 Elements output: 0 Execution time: 3.02 seconds

The table in SQL:

CREATE TABLE [dbo].[Raw_statestik](
    [Site] [nvarchar](100) NOT NULL,
    [Anvandarnamn] [nvarchar](50) NOT NULL,
    [Datum] [date] NOT NULL
) ON [PRIMARY]

I tested to create the Datum (date) as varchar, nvarchar as well.

If I would do output to CSV I will get the date and can read in the file manually to SQL. If I do raw output in powershell I see the date.

So this confuses me a bit, how can it want timestamp when it's only date? What am I missing? I remember I did this 5 years ago and had no problems at all.

Gatewarden
  • 391
  • 1
  • 3
  • 16
  • Did you try with a `[datetime]` column? I agree it shouldn't be necessary, but worth a try for sure? – Mathias R. Jessen Mar 08 '22 at 15:26
  • @MathiasR.Jessen I did, it works, but with the addition of 00:00:00 of course. It's a last resort but this should be working, hence my confusion. – Gatewarden Mar 08 '22 at 15:28
  • Wonder if this might be a driver issue as DATE is a new-ish data type(introduced in 2008) so may not be supported by older drivers. Did you try using the SQL Server Native Client (make sure to use appropriate version for your SQL Server)? See second answer on this post https://stackoverflow.com/questions/4849335/logparser-sql-table-column-x-data-type-is-not-compatible-with-select-clause-i – Stephan Mar 08 '22 at 18:53
  • consider using only T-SQL with `BULK INSERT` instead. you end up with separate date and time fields. I use it because I have to check for different languages and dates formats yyyy-mm-dd and dd/mm/yyyy, even mm/dd/yyyy – jjdesign Mar 08 '22 at 20:41
  • 1
    @Stephan I was considering it, it's version 11, but I did not have time to set ODBC-Connection before I had to leave yesterday. Will try it during the day today. – Gatewarden Mar 09 '22 at 06:42

1 Answers1

0

I went another way, I am processing the data to a view, and using cast to change it to a proper date. This means I use the datetime as the solution and remove the 00:00:00 with the cast.

Gatewarden
  • 391
  • 1
  • 3
  • 16