Search code examples
sql-serverodbcwindows-server-2012-r2logparser

LogParser SQL Server driver windows 2012 x64

I have a logparser one liner which outputs in a SQL Server database.

I had a conversion problem between logparser timestamp and SQL Server date(7) types. I resolved this issue by specifying the driver 

-driver "sql server native client 10.0"

Here is the whole oneliner :

LogParser "select to_date(to_timestamp('2015-03-13','yyyy-MM-dd')) AS REQ_DATE_DT, count(*) as REQ_REQUESTS_NU, 5 INTO MyTable from \\myServer\MyIISlog.log where time >= '12:00:00' and time <='14:59:59' " -i:IISW3C -o:SQL -createTable:OFF -server:MyServer\MyInstance -database:MyDB -username:myUser -password:MyPass -driver "sql server native client 10.0"

This command works well on my dev machine (windows 7 x64, SQL Server Management Studio installed). Now I want to run it from a script server (windows 2012r2 without ssms)

If I run the same command I have the following error :

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

If I change "SQL Server native client 10.0" to "SQL Server", I have the same error.

If I omit the -driver sql server part I have the usual convert error message

SQL table column "REQ_DATE_DT" data type is not compatible with SELECT clause item "REQ_DATE_DT" (type TIMESTAMP)

So the db server is reachable and can be queried with the credentials I gave.

I installed the odbc drivers from microsoft msi and they appear in both ODBC Data Source Administrator x32 and x64.

I tried the odbc server 10 (SQL Server 2008 R2) and 11 (SQL Server 2012)

What am I missing?

EDIT 1 :

I tested installing several sql features with no success

  • sqlncli, sqlCmdLnUtils and SharedManagementObjects (x64). I added the path to sqlcmd.exe to my $Path. The problem is still there, I can run sqlcmd and "Odbc Data Source" shows sql server 2008 driver (x86 and x64).
  • I installed some more features with no success. Here is the list of the component I installed (x64) : SQL 2008 Command Line Utilities, Managment Objects, Native Client, Replication Management Objects, SQLXML 4.0 SP1, SQL Server System CLR Types, Powershell Extensions for SQL Server 2008. All these are x64 features.
  • After installing the whole sql server stuff (including ssms, cli ...) I always encounter the same error

I'll try on other servers but I think it's a problem involving either only x64 sql native client or windows 2012r2 x64...

Solution

  • I managed to tackle around the problem using the -oConnString LogParser option instead of -driver -server -database ... :

    LogParser "select to_date(to_timestamp('2015-03-13','yyyy-MM-dd')) AS REQ_DATE_DT, count(*) as REQ_REQUESTS_NU, 5 INTO MyTable from \\myServer\MyIISlog.log where time >= '12:00:00' and time <='14:59:59' " -i:IISW3C -o:SQL -createTable:OFF -oConnString:"Driver={SQL server native client 10.0};server=MyServer;database=MyDB;uid=MyUser;pwd=MyUser;"

    Due to this workaround, I think the original problem is a matter of ODBC x86 and x64.

    Here are some questions that helped me :