Hi,

I'm in a bit of a problem getting the statistics to work.
I get this error:

Import Started. Failed SpyIISLog service. Exception: System.Runtime.InteropServices.COMException (0x80004005): Error executing query: Error connecting to ODBC Server SQL State: 28000 Native Error: 18456 Error Message: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'nyhiswwwshop01-usr'. [Unknown Error] at Interop.MSUtil.LogQueryClassClass.ExecuteBatch(String szQuery, Object pObjectInputContext, Object pObjectOutputContext) at Mediachase.eCF.Plugins.Services.Statistics.ImportIISLog.Execute() at Mediachase.eCF.Plugins.Services.Statistics.SpyIISLogService.ProcessData() at Mediachase.eCF.Plugins.Services.Statistics.SpyIISLogService.Execute()

I'm using the same user id and password for EcfSqlConnection as for EcfStatSqlConnection and the tables are in the same database. I'm not sure where to look for the source of this error. Could someone point me in the direction?

Gudmundur G.

Hi Gudmundur,

Please send us back the following info in the format below --

ECF Version: 4.1.2
Full Source Code License or Front-End Source only
Windows 2003 Server SP2
SQL Server 2005 Standard SP2
Visual Studio 2005 SP1

Did you ever succeed in getting Statistics to work in the past or is this the first time you are trying to configure it?  If it did work in the past, did you make any modifications and what were the steps that led to this result?

Have you tried searching the forums for configuration instructions and troubleshooting suggestions?  Here are a couple of posts that may be helpful to you.

http://www.mediachase.com/Community/forums/post/6428.aspx
http://www.mediachase.com/Community/forums/thread/7810.aspx

Mediachase Software

Hi,

ECF Version: 4.1.1
Full Source Code License
Windows 2003 SP2
SQL Server 2005 Standard SP1
Visual Studio 2008

The statistics have not worked before in this installation.
I've look for a solution in the forum but have not found anything that helps. I've also followed the steps listed by Alexey here but still not working.

The service tries for some minutes before finishing with the error. It seems to me that the service is able to read the logs but not able to write it into the SQL tables.

/GG

Hi,

Was this a clean install of 4.1.1 or did you upgrade from an older release? 

Mediachase Software

Please set breakpoint in ProcessIISLog.cs file, ProcessData method on line 102 (import.ConnectionString = _ConnectionString;) and make sure that the _ConnectionString parameter is correct.

I'm not able to debug the code in the running environment since it's running on a remote site without dev tools but I added a log at this point in the code and confirmed the connection string to be correct.

The installation is not a "clean install" but a manual install.

/GG

Here are more detailed instructions on our documentation site - http://docs.mediachase.com/doku.php?id=ecf:41:tutorials:stats

Hope this helps.

Mediachase Software

Hi,
Thanks for the link to the documentation.
I've followed it step by step to make sure everything was set up correctly, but I still get the error.

(I noticed what I believe is an error, in the last bullet of the detailed documentation:
The file Mediachase.ImportIISLog.dll should be Mediachase.eCommerce.ImportIISLog.dll,
I tried to comment on the wiki but got an error in the Wiki system...)

/GG

Hi Gudmundur,

First of all, I thank you for letting us know about the error in our documentation - it has been changed. We really appreciate it.

We checked our release history and there was an error with statistics in ECF 4.1.1 which was fixed in our latest release, ECF 4.1.2. You can read more about it here - http://www.mediachase.com/ecf/releasenotes.aspx

Here are upgrade instructions - http://docs.mediachase.com/doku.php?id=ecf:41:tutorials:upgrading
On that page, you will notice that there is a link to download the most recent SQL scripts. One script called function.sql is not in the original ECF 4.1.2 package so you will need to run that script first.

Please backup your database before running any of these SQL scripts. Let us know how it goes.

Mediachase Software

Thanks,
Looking at the release notes I realized that I gave you the wrong version number of the release we're using.
We are using 4.1.0 but not 4.1.1.
Upgrading to 4.1.2 is something we need to do but it will take some time and is not planned quite yet since we need to merge the code from the 4.1.2 with our own modifications.
Was the error mentioned being in 4.1.1 also in 4.1.0?
If yes, can I fix the error with a patch script without upgrading everything?

/GG

fyi, the site in question is here: www.netverslun.is

Try to achieve successful performance of a following command (installation of program LogParser is required):

LogParser.exe "SELECT TOP 1 TO_LOCALTIME( TO_TIMESTAMP(date, time)) AS DateTime INTO LogParserTestTable FROM %windir%\\system32\\Logfiles\\W3SVC1\\ex*.log" -i:IISW3C -server:{SQLServer} -database:{SQLDatabase} -driver:"SQL Server" -username:{SQLUser} -password:{SQLPassword} -createTable:ON

Alexey,
Mediachase

This test is capable to reveal problems of authentification with SQL server.

OK, I ran this successfully, and I confirmed the new table LogParserTestTable to include a single field DateTime with the date and time from the first line of the first log file in the log directory.


/GG

Can you please send your web.config file to:  aa@mediachase.com ?
It is necessary will make sure of correctness of connection string format.

The problem became clear. When SpyIISLogService translate SQL Connection String to ODBC format, connection string together with password symbols is transformed to the lower case. Try to use password string not sensitive to the register or change connection string transformation in ProcessIISLog.cs and recompile SpyIISLog service.

Thank you!
Alexey