Database Logging

Topics: Semantic Logging Application Block
Nov 30, 2014 at 6:59 PM
Edited Nov 30, 2014 at 7:30 PM
Hi There,

I'm testing the Semantic Logging and trying to setup a database log in a console application with the following command:
var cs = ConfigurationManager.ConnectionStrings["Traces"];
var listener = SqlDatabaseLog.CreateListener( "GEOVIXEL", cs.ConnectionString,
  bufferingInterval: TimeSpan.FromSeconds( 3 ), bufferingCount:10 );
listener.EnableEvents( GvxEventSource.Log, EventLevel.Verbose, Keywords.All );
(Yes the database exists and yest the table and stored procedure are created.)

The connection string is coming from the app.config as follow:
  <add name="Traces" connectionString="Data Source=(localDB)\v11.0;Initial Catalog=TestLog; Integrated Security=True" providerName="System.Data.SqlClient" />
I also setup a console and flat file logs that works well.

I'm not getting any error whit the setup but the table is empty. (Yes I dispose and even wait 10 seconds before exit to make sure).

The funny part is that it works fine in the out-of-process logging with the following configuration:
<!-- Database Events -->
    name             ="GEOVIXEL-SQL"
    instanceName     ="GEOVIXEL"
    connectionString ="Data Source=(localDB)\v11.0;Initial Catalog=TestLog;Integrated Security=True">
            name  ="GEOVIXEL"
            level ="Verbose"
What am I doing wrong? It's one line of code configuration!!! Using the same connection string!

UPDATE: Adding the FLushAsync before exit I can see now that there's an exception that is thrown: "Trying to pass a table-valued parameter with 16 column(s) where the corresponding user-defined table type requires 12 column(s)."

The table does have 12 columns. Why the logger's trying to write 16? Do I have the correct script to create the table?
CREATE TABLE [dbo].[Traces](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [InstanceName] [nvarchar](1000) NOT NULL,
    [ProviderId] [uniqueidentifier] NOT NULL,
    [ProviderName] [nvarchar](500) NOT NULL,
    [EventId] [int] NOT NULL,
    [EventKeywords] [bigint] NOT NULL,
    [Level] [int] NOT NULL,
    [Opcode] [int] NOT NULL,
    [Task] [int] NOT NULL,
    [Timestamp] [datetimeoffset](7) NOT NULL,
    [Version] [int] NOT NULL,
    [FormattedMessage] [nvarchar](4000) NULL,
    [Payload] [nvarchar](4000) NULL,
    [id] ASC
Nov 30, 2014 at 7:44 PM
Reply to myself!

Yes there is an issue with the schema of the database.

The scripts in: EnterpriseLibrary6-binaries.nuget\packages\EnterpriseLibrary.SemanticLogging.Database.1.0.1304.0\scripts with the source code is a 13 columns table while the script WITH the nuget package has the 17 columns schema.

Please update the source code package with the most recent script!

Nov 30, 2014 at 7:54 PM
If the OOP Service is working then the EventSource should be setup properly.

There are two methods of inserting to the Traces table: Stored Procedure and SqlBulkCopy. So it is possible for some scenarios to work and others to fail depending on the number of messages buffered. However, for a simple test the Stored Procedure would be used.

It sounds like there is some issue in connecting to the database. I would guess it's probably the credentials used. To check you can use the following code to enable the SemanticLoggingEventSource provider:
var cs = ConfigurationManager.ConnectionStrings["Traces"];
var listener = SqlDatabaseLog.CreateListener("GEOVIXEL", cs.ConnectionString,
    bufferingInterval: TimeSpan.FromSeconds(3), bufferingCount: 10);

listener.EnableEvents(GvxEventSource.Log, EventLevel.Verbose, Keywords.All);

// Output any sink errors to the console
var consoleListener = ConsoleLog.CreateListener();
// Or whatever events you have defined


You should see some error messages published by the sink.

Randy Levy
Enterprise Library support engineer
Support How-to
Nov 30, 2014 at 8:52 PM
Now I get it!

Since the out-of-process logging doesn't have the information from etw for the columns ActivityId, RelatedActivityId, ProcessId, and ThreadId, it uses a 13 columns table instead instead of the 17 columns for in-process logging. I would have used nullable columns to keep the schema consistent between in and out process so we can by configuration switch from one to the other but hey, that's just me.

Hopes my journey help someone else!

Nov 30, 2014 at 10:15 PM
It sounds to me like you may be mixing different versions of the Semantic Logging Application Block.

The actual sink implementation used in-process and out-of-process is the same so they both should be using the same column definitions provided that the same assembly versions are being used in-process as out-of-process. Usually, I would think that the OOP Service would be installed in a separate directory as the source application so it would have a separate copy of the Microsoft.Practices.EnterpriseLibrary.SemanticLogging.Database.dll assembly. So, if a new version of SLAB was being used (e.g. from NuGet) then you would want to make sure that you upgrade any OOP services to use the new version. This is only really an issue for sinks that interface with a "service"/backing store.

All the SLAB 1.1 (e.g. ActivityId) columns are nullable.

Randy Levy
Enterprise Library support engineer
Support How-to
Dec 1, 2014 at 9:01 PM
Hi Randy,

It is a mix up of version indeed.

Somehow when I downloaded Enterprise Library 6 I got the following files:


The SemanticLogging-service.exe package included in the group is version 1.0.1304 while the current version is 2.0.1406.

The version 2.0 works perfectly.

Dec 1, 2014 at 11:28 PM
Good to hear!