Database Trace Listener config for Exception Handler

Topics: Exception Handling Application Block, Logging Application Block
Jul 27, 2007 at 6:13 PM
Where can I find a clear description of how to use the Exception Handling Block ( 2.0) with a database trace listener.

The Flat File Trace listener works fine for me. But I must be missing something when trying to utilize the Database Trace Listener.

I have created my Logging database, tables and procs using the script LoggingDatabase.sql from the EAB source code folder.

I have run the WriteLog proc from Query analyzer and can write a record into the Log table.

I have configured the DB tracelistener to use the correct connect string, and the WriteLog proc.

But when i call that exception policy from the catch block in my code, it never writes a record. No errors occur (other than the exception I am testing with). It just doesn't write the record.

I am left with several questions:

1. Do I need any code in my app (beyond the ExceptionPolicy.HandleException(ex, "Exception Policy"); statement in my exception catch block?
2. What is the purpose of the "Category tables"? How are they supposed to be configured?
3. Why is there AIR?! (just kidding on the last one)

Can someone demystify this form me?

I will be eternally grateful.

Joe Rich


Jul 30, 2007 at 4:12 PM
Hi Joe,

I suggest you start by making sure you can get to the DB directly from the logging block, instead of going through the logging EH handler to remove the extra step.

The logging block will let you route messages to handlers so it's not enough to just configure the handler; you also need to instruct the "sources" to use this handler. You don't describe this configuration step in your post, so it's likely missing.

Please refere to the Logging Block topic in the integrated help, or this page in msdn http://msdn2.microsoft.com/en-us/library/Aa480464.aspx for further details.

Regarding your questions:
1. Your code should not need to be aware of the exception handling details, so it should be the same regardless of how the blocks are configured.
2. The category tables are not used for configuration; they are populated while logging.

Regards,
Fernando
Jul 30, 2007 at 7:25 PM
Fernando,

Thank you for attempting to aswer my question, but I think we are missing the mark. Let me be explcit.

I have done the following:

    1. Located the script LoggingDatabase.sql in the subdirectory C:\Program Files\Microsoft Enterprise Library January 2006\src\Logging\TraceListeners\Database\Scripts.
    2. Run this script against my server.
    3. Created a DataBase trace listener that uses the connect string for the "Logging" database created by the script and uses the System.Data.SqlClient provider.
    4. Formatter: Text Formatter
    5. TraceOutputOptions: None
    6. WriteLogStoredProcedureName: WriteLog
    7. Given the instance the name: "Database Trace Listener"
    8. Under Logging Errors & Warnings I have specified the Trace Listener to be: "Database Trace Listener"

From the documentation, I think this is all I need to do to cause an error to be logged via the Database Trace Listener. However, nothing is written to the database when an error occurs.

Now I know I must be missing a step, but from the documetation, the nature of this missing step is not at all obvioius.

Can you help?

Joe Rich
Jul 30, 2007 at 8:08 PM
Hi Joe,

I don't know which part of the documentation you're referring to. Can you please post the topic name?

You need to keep in mind that the logging exception handler is a client to the logging block, and it will post logging entries based on its configuration. The logging block will process the entry just like it would for entries from any other client by routing it to the appropriate trace listener based on the entry category information, as indicated by the logging block's configuration. Entries posted by the logging handler are not errors as far as the logging block is concerned. You need to link the db trace listener to the category that will be used by the logging handler when posting entries; in your case the handler's category is most likely the logging block's default category, unless you changed it in the handler's configuration, so you'll need to add the db trace listener to the "General" category in the logging block unless again you changed this default. The "Logging Errors & Warnings" category is used to log errors internal to the logging block.

Regards,
Fernando
Sep 25, 2007 at 4:53 PM
Same problem here. Checked, double checked and then checked some more... but to no avail. No records are put into table. What's wrong?
Sep 25, 2007 at 6:47 PM
Hi,


nospam wrote:
Same problem here. Checked, double checked and then checked some more... but to no avail. No records are put into table. What's wrong?


The original poster's scenario involved the LoggingExceptionHandler. Is this your case?

Here's a minimal repro I used. Please try it and post how it works for you.

Program

static void Main(string[] args)
{
Logger.Write("this is a test", "General", 0, 100);
}


Config (empty config + database trace listener + General category poiting to the new trace listener instead of the default file one)

<?xml version="1.0" encoding="utf-8"?>
<configuration>
<configSections>
<section name="loggingConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Logging.Configuration.LoggingSettings, Microsoft.Practices.EnterpriseLibrary.Logging, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
<section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
</configSections>
<loggingConfiguration name="Logging Application Block" tracingEnabled="true"
defaultCategory="General" logWarningsWhenNoCategoriesMatch="true">
<listeners>
<add databaseInstanceName="Logging" writeLogStoredProcName="WriteLog"
addCategoryStoredProcName="AddCategory" formatter="Text Formatter"
listenerDataType="Microsoft.Practices.EnterpriseLibrary.Logging.Database.Configuration.FormattedDatabaseTraceListenerData, Microsoft.Practices.EnterpriseLibrary.Logging.Database, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
traceOutputOptions="None" type="Microsoft.Practices.EnterpriseLibrary.Logging.Database.FormattedDatabaseTraceListener, Microsoft.Practices.EnterpriseLibrary.Logging.Database, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
name="Database Trace Listener" />
<add source="Enterprise Library Logging" formatter="Text Formatter"
log="Application" machineName="" listenerDataType="Microsoft.Practices.EnterpriseLibrary.Logging.Configuration.FormattedEventLogTraceListenerData, Microsoft.Practices.EnterpriseLibrary.Logging, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
traceOutputOptions="None" type="Microsoft.Practices.EnterpriseLibrary.Logging.TraceListeners.FormattedEventLogTraceListener, Microsoft.Practices.EnterpriseLibrary.Logging, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
name="Formatted EventLog TraceListener" />
</listeners>
<formatters>
<add template="Timestamp: {timestamp}&#xD;&#xA;Message: {message}&#xD;&#xA;Category: {category}&#xD;&#xA;Priority: {priority}&#xD;&#xA;EventId: {eventid}&#xD;&#xA;Severity: {severity}&#xD;&#xA;Title:{title}&#xD;&#xA;Machine: {machine}&#xD;&#xA;Application Domain: {appDomain}&#xD;&#xA;Process Id: {processId}&#xD;&#xA;Process Name: {processName}&#xD;&#xA;Win32 Thread Id: {win32ThreadId}&#xD;&#xA;Thread Name: {threadName}&#xD;&#xA;Extended Properties: {dictionary({key} - {value}&#xD;&#xA;)}"
type="Microsoft.Practices.EnterpriseLibrary.Logging.Formatters.TextFormatter, Microsoft.Practices.EnterpriseLibrary.Logging, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
name="Text Formatter" />
</formatters>
<categorySources>
<add switchValue="All" name="General">
<listeners>
<add name="Database Trace Listener" />
</listeners>
</add>
</categorySources>
<specialSources>
<allEvents switchValue="All" name="All Events" />
<notProcessed switchValue="All" name="Unprocessed Category" />
<errors switchValue="All" name="Logging Errors & Warnings">
<listeners>
<add name="Formatted EventLog TraceListener" />
</listeners>
</errors>
</specialSources>
</loggingConfiguration>
<connectionStrings>
<add name="Logging" connectionString="Data Source=(local)\SQLEXPRESS;Initial Catalog=Logging;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>


Sample output

D:\delete\ReproDbLog\ReproDbLog\bin\Debug>osql -S (local)\SQLEXPRESS -d Logging-E -Q "select substring(message, 1, 20) from Log"

--------------------

(0 rows affected)

D:\delete\ReproDbLog\ReproDbLog\bin\Debug>ReproDbLog.exe

D:\delete\ReproDbLog\ReproDbLog\bin\Debug>osql -S (local)\SQLEXPRESS -d Logging-E -Q "select substring(message, 1, 20) from Log"

--------------------
this is a test

(1 row affected)

D:\delete\ReproDbLog\ReproDbLog\bin\Debug>
Oct 6, 2009 at 7:41 PM
Edited Oct 6, 2009 at 7:41 PM

I'm having the exact same problem as Joseph. Flat file works, database does not. What's strange is when I try to test my connection from the entlib config tool, it tells me the Logging database does not exist, or it is not accessible. The database is definitely there, but it seems it's having trouble authenticating. I'm using integrated authentication, and this could be a problem, but if it's the case then why? /spinninghead

Oct 6, 2009 at 8:33 PM
Edited Oct 6, 2009 at 8:33 PM

Alright, so the tool was bugged. Anyone having the same problem should delete the data access application block node, and recreate it. This will most likely fix the problem.

Nov 24, 2009 at 11:32 PM
JoshuaRamirez wrote:

I'm having the exact same problem as Joseph. Flat file works, database does not. What's strange is when I try to test my connection from the entlib config tool, it tells me the Logging database does not exist, or it is not accessible. The database is definitely there, but it seems it's having trouble authenticating. I'm using integrated authentication, and this could be a problem, but if it's the case then why? /spinninghead

I am facing the same issue. I am using 4.1 Version. I am able to see the Flat File with results. But no entry in DB. I am actually using Custom Properties in Text Format. 

sp_Name: {property(sp_Name)}

category:{category}
WebPage: {property(WebPage)}
LineNo: {property(LineNo)}
Severity: {severity}
Message: {message}
HTTPMessage: {property(HTTPMessage)}
LogType: {property(LogType)}
Job_User: {property(Job_User)}
Job_Date: {property(Job_Date)}
Timestamp:{timestamp}
Extended Properties: {dictionary({key} - {value} )}

sp_Name: {property(sp_Name)}

category:{category}

WebPage: {property(WebPage)}

LineNo: {property(LineNo)}

Severity: {severity}

Message: {message}

HTTPMessage: {property(HTTPMessage)}

LogType: {property(LogType)}

Job_User: {property(Job_User)}

Job_Date: {property(Job_Date)}

Timestamp:{timestamp}

Extended Properties: {dictionary({key} - {value} )}

 

Nov 25, 2009 at 2:32 AM

Check the destination log of the tracelistener you configured under Logging Errors and Warnings, see if there's any error logs containing details as to why logging to the database failed.  The message contains this statement : "Tracing to LogSource '<databaseTraceListenerCategory>' failed. Processing for other sources will continue...." I'd also suggest you ran a sql profiler and see if the WriteLog stored procedure was executed.

 

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

Jan 26, 2010 at 6:36 PM

I also had the similar problem. This is what I have done to figure it out. I have added an event log trance listener to "Logging Errors & Warnings" category, so that I can see the exact error occuring while logging to the database. I found a database exception "Cannot open user default database. login failed". So it was a database issue. I fixed it and it all worked.