Writelog procedure returns a bigint instead of an int

Topics: Logging Application Block
Sep 19, 2014 at 6:51 PM

We are using Enterprise Library 5 for our logging, tracing and exception handling. It works beautifully. However as part of our logging logic now we are about to run into trouble. So, we using writelog stored proc to add log entries into the log table in our sql 2008 database. The table has Logid as a primary key which is also an identity key. The value of this key is about to cross the maximum size of Integer and we decided to change the datatype of this column to bigint. The problem is when we changed the writelog procedure. We are setting the output parameter to @@identity but are getting data type conversion error. The datatype of @logid output parameter has been changed to bigint. Is it not possible to set this value to bigint?

Exact error:
Failed to convert bigint to int
Sep 22, 2014 at 3:40 AM
That's a lot of LogEntries (2 billion+)! The issue is that in the Enterprise Library source code LogId is an Int32 and not Int64. You have 4 options that I can think of:
  1. Change the Enterprise Library source code to use an Int64
  2. Create a custom trace listener that does exactly what you want (similar to option 1 but without changing the out-of-the-box source code)
  3. Archive the logging tables and restart logging again with fresh identity values
  4. Do something else
Here is one "option 4". Use BigInt in the database but return LogId as an Int. This can work because the Enterprise Library C# code doesn't do anything with the LogId except pass it to the category stored procedure. So, keep the logging table as BigInt. Change the CategoryLog table LogId column to also be a BigInt. In the WriteLog stored procedure return an int instead of a BigInt (e.g. set @LogId = @@IDENTITY - 2147483648) and in the InsertCategoryLog stored procedure perform the reverse operation to get the actual LogId. This is only a temporary fix since it will eventually overflow as well.

Another variation, if you don't care about the category information, would be to return the LogId as an int and have the category stored procedures ignore it (since you wouldn't be able to correlate the int value to the actual BigInt LogId inserted).

Randy Levy
Enterprise Library support engineer
Support How-to
Sep 24, 2014 at 5:15 PM
Thanks a lot Randy. We have decided to go with option 3. We were already archiving the log table but were not resetting the identity key. It took us around 3 years to reach a level where we needed bigint. Creating custom listener would have forced us to do a full regression testing, which is not worth it at this point. By the way, why don't you make data type also customizable? Just like other settings, it can read this as well from config file?Thanks gain for the reply though.