SqlServerCompactErrorLog reports wrong count of errors

[GoogleCodeExporter]

What steps will reproduce the problem?

1. Install and configure ELMAH to use SqlServerCompactErrorLog (e.g. using 
elmah.sqlservercompact[1] NuGet package)
2. Generate some errors, say about 5
3. Call up the error log display and note that total count matches 5 and that 
all those 5 errors show up.
4. Change the application name, easiest way being via configuration
5. Generate some more errors, say another 3
6. Call up the error log display

What is the expected output? What do you see instead?

The total count and number of errors shown should match up. Instead, the total 
count is 8 and number of errors that are list are 3.

It seems that that the total count does not take into consideration the 
application name whereas retrieval of a page of errors does. The total count 
query looks like this:

  SELECT COUNT(*) FROM [ELMAH_Error]

Retrieval of a page of errors has a WHERE clause reading:

  WHERE [Application] = @Application

This results in inconsistent reporting, whether or not isolating errors by 
application name was the intended behavior. Given that 
SqlServerCompactErrorLog.GetError is also implemented without filtering on 
application name, it seems that the intended behavior was single application 
use; isolation was unintended so the WHERE clause should be removed when 
retrieving a page of errors.

There are several workarounds possible:

  1. Explicitly configure the application name to be always the same. This works for cases where the log is not shared across applications on the same box.
  2. Clear the error log each time the application name is expected to change. The time at which to do this can be difficult to guess especially when the application name is not explicitly configured and left to be inferred.
  3. Manually update the Application column by issuing an UPDATE statement in SQL. The time at which to do this can be difficult to guess especially when the application name is not explicitly configured and left to be inferred.
  4. Write a local implementation of SqlServerCompactErrorLog that overrides GetErrors and removes the aforementioned WHERE clause that filters on the application name. This makes SqlServerCompactErrorLog suitable for a single application use only. One such implementation is attached and posted online[2].

Additional information:

- ELMAH 1.2 SP 1 release

At this point, there is no plan for an SP 2 and a reasonable set of workarounds 
is available. As a result, I think this should be fixed in the Hg repo, which 
contains code working towards 2.0.

[1] http://nuget.org/packages/elmah.sqlservercompact
[2] https://gist.github.com/1681836

Original issue reported on code.google.com by azizatif on 26 Jan 2012 at 9:10

Attachments:

• SqlServerCompactErrorLog.cs

[GoogleCodeExporter]

Hi Aziz,
I am not a Hg expert, will you remove the WHERE clause in the version 2.0 code?

Original comment by ejls...@hotmail.com on 26 Jan 2012 at 9:20

[GoogleCodeExporter]

Erik, I can take care of that. Can you comment on whether or not you had 
intended application isolation in SqlServerCompactErrorLog?

Original comment by azizatif on 26 Jan 2012 at 10:51

• Added labels: Milestone-Release2.0

[GoogleCodeExporter]

I only intended the implementation to support a single application. As the 
database resides in the application folder structure anaway (App_Data)

Original comment by ejls...@hotmail.com on 26 Jan 2012 at 11:58

[GoogleCodeExporter]

The App_Data is just the preferred location for a default setup in an ASP.NET 
web site. It does not preclude putting it somewhere else on the machine. I 
really got wondering because SQL Server Compact supports "opening connections 
on different processes" and the idea to permit SqlServerCompactErrorLog as a 
shareable machine-wide store was floated at some point.

Original comment by azizatif on 26 Jan 2012 at 1:12

[GoogleCodeExporter]

Just my two pennies/cents on this:

Isn't part of the philosophy behind SQL Server Compact that it provides an 
upgrade path to full blown SQL Server?

In which case, does it not make sense to emulate all the features of 
SqlErrorLog in SqlServerCompactErrorLog??

Original comment by jamesdriscoll71 on 26 Jan 2012 at 1:21

[GoogleCodeExporter]

I agree, James, it makes sense to fully emulate the full SQL Server (and allow 
migration), but opening the same database from different web apps will require 
all apps to use the EXACT same connection string. Not sure if that could become 
an issue? If not, that is what any fix should go for (include app in filters 
and logging)

Original comment by ejls...@hotmail.com on 26 Jan 2012 at 1:38

[GoogleCodeExporter]

Original comment by azizatif on 26 Jan 2012 at 8:23

• Changed state: Accepted

[GoogleCodeExporter]

SQL Server Compact is just another error log storage option as far as this 
project (ELMAH) is concerned. There is no upgrade path being endorsed between 
any of the storage options so far and it's a discussion that's beyond this 
scope of this particular bug. SqlServerCompactErrorLog, as it stands right now, 
provides a nice alternative to SQLiteErrorLog considering seamless 32-bit and 
64-bit deployment, partial trust environment and tools support.

Original comment by azizatif on 26 Jan 2012 at 8:36

[GoogleCodeExporter]

This issue was closed by revision 9918ae667f2c.

Original comment by azizatif on 26 Jan 2012 at 8:44

• Changed state: Fixed

[GoogleCodeExporter]

Re-opened as started and re-scheduling for 1.2 SP2

Original comment by azizatif on 24 Feb 2012 at 9:48

• Changed state: Started
• Added labels: Milestone-Release1.2SP2
• Removed labels: Milestone-Release2.0

[GoogleCodeExporter]

This issue was closed by revision 194c41ccdd84.

Original comment by azizatif on 4 Mar 2012 at 9:52

• Changed state: Fixed