HOW to log exception details into database?

Topics: Data Access Application Block, Exception Handling Application Block, Logging Application Block
Jun 8, 2009 at 12:55 PM

Hello,

I am using following App Blocks(EntLib 4.1) in my project -

(1). Exception Handling AB (2) Data Access AB and (3) Logging AB

I have configured my application for logging exception details in flat files and also I am receiving it through e-mail  

Now I want to configure my application for logging exception details into SQL server database, but it's not working with following config.

I have added following ref files in my project:

- Microsoft.Practices.EnterpriseLibrary.Logging.Database.dll

- Microsoft.Practices.EnterpriseLibrary.Common

- Microsoft.Practices.EnterpriseLibrary.ExceptionHandling

- Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.Logging

- Microsoft.Practices.ObjectBuilder2

ALSO I have created req. tables and stored procs in DDB database.

I will present some code here for ref: config code and CS code resp.

My config file ->

 <?xml version="1.0"?><font size="2" color="#0000ff">

<

</font>

configuration><font size="2" color="#0000ff">

<

</font>

configSections><font size="2" color="#0000ff">

<

</font>

section name="loggingConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Logging.Configuration.LoggingSettings, Microsoft.Practices.EnterpriseLibrary.Logging, Version=4.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" /><font size="2" color="#0000ff">

<

</font>

section name="exceptionHandling" type="Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.Configuration.ExceptionHandlingSettings, Microsoft.Practices.EnterpriseLibrary.ExceptionHandling, Version=4.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" /><font size="2" color="#0000ff">

<

</font>

section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=4.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" /><font size="2" color="#0000ff">

<

</font>

section name="exceptionManagement" type="Microsoft.ApplicationBlocks.ExceptionManagement.ExceptionManagerSectionHandler,Microsoft.ApplicationBlocks.ExceptionManagement" />

 </configSections>

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

<font size="2" color="#0000ff">

<

</font>

loggingConfiguration name="Logging Application Block" tracingEnabled="true"

<font size="2">

 

</font>

 

defaultCategory="General" logWarningsWhenNoCategoriesMatch="true"><font size="2" color="#0000ff">

<

</font>

listeners><font size="2" color="#0000ff">

<

</font>

add databaseInstanceName="ConnectionString" writeLogStoredProcName="WriteLog"

<font size="2">

 

</font>

 

addCategoryStoredProcName="AddCategory" formatter="" listenerDataType="Microsoft.Practices.EnterpriseLibrary.Logging.Database.Configuration.FormattedDatabaseTraceListenerData, Microsoft.Practices.EnterpriseLibrary.Logging.Database, Version=4.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"<font size="2">

 

</font>

 

traceOutputOptions="None" filter="All" type="Microsoft.Practices.EnterpriseLibrary.Logging.Database.FormattedDatabaseTraceListener, Microsoft.Practices.EnterpriseLibrary.Logging.Database, Version=4.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"<font size="2">

 

</font>

 

name="Database Trace Listener" /><font size="2" color="#0000ff">

<

</font>

add toAddress=Vired.A@rox.com fromAddress=rox.note@rox.com

<font size="2">

 

</font>

 

subjectLineStarter="" subjectLineEnder="" smtpServer="smtprelay.rox.com"<font size="2">

 

</font>

 

smtpPort="25" formatter="Email Formatter" listenerDataType="Microsoft.Practices.EnterpriseLibrary.Logging.Configuration.EmailTraceListenerData, Microsoft.Practices.EnterpriseLibrary.Logging, Version=4.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"<font size="2">

 

</font>

 

traceOutputOptions="None" filter="Information" type="Microsoft.Practices.EnterpriseLibrary.Logging.TraceListeners.EmailTraceListener, Microsoft.Practices.EnterpriseLibrary.Logging, Version=4.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"<font size="2">

 

</font>

 

name="Email TraceListener" /><font size="2" color="#0000ff">

<

</font>

add fileName="C:\Inetpub\wwwroot\DASH2.0\DASH\logs\trace.log"

<font size="2">

 

</font>

 

 

<font size="2">

 

</font>

 

formatter="Text Formatter" listenerDataType="Microsoft.Practices.EnterpriseLibrary.Logging.Configuration.FlatFileTraceListenerData, Microsoft.Practices.EnterpriseLibrary.Logging, Version=4.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"<font size="2">

 

</font>

 

traceOutputOptions="None" filter="Information" type="Microsoft.Practices.EnterpriseLibrary.Logging.TraceListeners.FlatFileTraceListener, Microsoft.Practices.EnterpriseLibrary.Logging, Version=4.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"<font size="2">

 

</font>

 

name="FlatFile TraceListener" /><font size="2" color="#0000ff">

<

</font>

add source="Enterprise Library Logging" formatter="Text Formatter"

<font size="2">

 

</font>

 

log="Application" machineName="" listenerDataType="Microsoft.Practices.EnterpriseLibrary.Logging.Configuration.FormattedEventLogTraceListenerData, Microsoft.Practices.EnterpriseLibrary.Logging, Version=4.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"<font size="2">

 

</font>

 

traceOutputOptions="None" filter="All" type="Microsoft.Practices.EnterpriseLibrary.Logging.TraceListeners.FormattedEventLogTraceListener, Microsoft.Practices.EnterpriseLibrary.Logging, Version=4.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"<font size="2">

 

</font>

 

name="Formatted EventLog TraceListener" /><font size="2" color="#0000ff">

</

</font>

listeners><font size="2" color="#0000ff">

<

</font>

formatters><font size="2" color="#0000ff">

<

</font>

add template="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;)}"

<font size="2">

 

</font>

 

type="Microsoft.Practices.EnterpriseLibrary.Logging.Formatters.TextFormatter, Microsoft.Practices.EnterpriseLibrary.Logging, Version=4.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" name="Email Formatter" /><font size="2" color="#0000ff">

<

</font>

add template="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;)}"

<font size="2">

 

</font>

 

type="Microsoft.Practices.EnterpriseLibrary.Logging.Formatters.TextFormatter, Microsoft.Practices.EnterpriseLibrary.Logging, Version=4.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" name="Text Formatter" /><font size="2" color="#0000ff">

</

</font>

formatters><font size="2" color="#0000ff">

<

</font>

categorySources>

<add switchValue="Off" name="General"><font size="2" color="#0000ff">

<

</font>

listeners><font size="2" color="#0000ff">

<

</font>

add name="Formatted EventLog TraceListener" /><font size="2" color="#0000ff">

</

</font>

listeners><font size="2" color="#0000ff">

</

</font>

add><font size="2" color="#0000ff">

<

</font>

add switchValue="All" name="LoggingCategory"><font size="2" color="#0000ff">

<

</font>

listeners><font size="2" color="#0000ff">

<

</font>

add name="Database Trace Listener" /><font size="2" color="#0000ff">

<

</font>

add name="Email TraceListener" /><font size="2" color="#0000ff">

<

</font>

add name="FlatFile TraceListener" /><font size="2" color="#0000ff">

</

</font>

listeners><font size="2" color="#0000ff">

</

</font>

add><font size="2" color="#0000ff">

</

</font>

categorySources><font size="2" color="#0000ff">

<

</font>

specialSources><font size="2" color="#0000ff">

<

</font>

allEvents switchValue="All" name="All Events"><font size="2" color="#0000ff">

<

</font>

listeners><font size="2" color="#0000ff">

<

</font>

add name="Database Trace Listener" /><font size="2" color="#0000ff">

</

</font>

listeners><font size="2" color="#0000ff">

</

</font>

allEvents><font size="2" color="#0000ff">

<

</font>

notProcessed switchValue="All" name="Unprocessed Category" /><font size="2" color="#0000ff">

<

</font>

errors switchValue="All" name="Logging Errors &amp; Warnings"><font size="2" color="#0000ff">

<

</font>

listeners><font size="2" color="#0000ff">

<

</font>

add name="Database Trace Listener" /><font size="2" color="#0000ff">

</

</font>

listeners><font size="2" color="#0000ff">

</

</font>

errors><font size="2" color="#0000ff">

</

</font>

specialSources><font size="2" color="#0000ff">

</

</font>

loggingConfiguration><font size="2" color="#0000ff">

<

</font>

exceptionHandling><font size="2" color="#0000ff">

<

</font>

exceptionPolicies><font size="2" color="#0000ff">

 

</font>

<add name="DAL Policy"><font size="2" color="#0000ff">

<

</font>

exceptionTypes><font size="2" color="#0000ff">

<

</font>

add type="System.Exception, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"

<font size="2">

 

</font>

 

postHandlingAction="ThrowNewException" name="Exception"><font size="2" color="#0000ff">

<

</font>

exceptionHandlers><font size="2" color="#0000ff">

<

</font>

add logCategory="LoggingCategory" eventId="100" severity="Error"

<font size="2">

 

</font>

 

title="Exception Occured in DAL" formatterType="Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.TextExceptionFormatter, Microsoft.Practices.EnterpriseLibrary.ExceptionHandling, Version=4.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"<font size="2">

 

</font>

 

priority="0" useDefaultLogger="false" type="Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.Logging.LoggingExceptionHandler, Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.Logging, Version=4.1.0.0, Culture=neutral, PublicKeyToken=31bf3856a364e35"<font size="2">

 

</font>

 

name="Logging Handler" /><font size="2" color="#0000ff">

<

</font>

add exceptionMessage="Exception Occured in DAL" exceptionMessageResourceType=""

<font size="2">

 

</font>

 

exceptionMessageResourceName="" wrapExceptionType="System.Exception, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"<font size="2">

 

</font>

 

type="Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.WrapHandler, Microsoft.Practices.EnterpriseLibrary.ExceptionHandling, Version=4.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"<font size="2">

 

</font>

 

name="Wrap Handler" /><font size="2" color="#0000ff">

</

</font>

exceptionHandlers><font size="2" color="#0000ff">

</

</font>

add><font size="2" color="#0000ff">

</

</font>

exceptionTypes><font size="2" color="#0000ff">

</

</font>

add><font size="2" color="#0000ff">

<

</font>

add name="Logging Policy"><font size="2" color="#0000ff">

<

</font>

exceptionTypes><font size="2" color="#0000ff">

<

</font>

add type="System.Exception, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"

<font size="2">

 

</font>

 

postHandlingAction="None" name="Exception"><font size="2" color="#0000ff">

<

</font>

exceptionHandlers><font size="2" color="#0000ff">

<

</font>

add logCategory="LoggingCategory" eventId="100" severity="Error"

<font size="2">

 

</font>

 

title="Enterprise Library Exception Handling" formatterType="Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.TextExceptionFormatter, Microsoft.Practices.EnterpriseLibrary.ExceptionHandling, Version=4.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"<font size="2">

 

</font>

 

priority="0" useDefaultLogger="false" type="Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.Logging.LoggingExceptionHandler, Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.Logging, Version=4.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"<font size="2">

 

</font>

 

name="Logging Handler" /><font size="2" color="#0000ff">

</

</font>

exceptionHandlers><font size="2" color="#0000ff">

</

</font>

add><font size="2" color="#0000ff">

</

</font>

exceptionTypes><font size="2" color="#0000ff">

</

</font>

add><font size="2" color="#0000ff">

</

</font>

exceptionPolicies><font size="2" color="#0000ff">

</

</font>

exceptionHandling>

<connectionStrings><font size="2" color="#0000ff">

<

</font>

add name="ConnectionString" connectionString="Data Source=D09;Initial Catalog=Da2;User ID=qIntranet;Password=qIntranet;Connection Reset=true;Enlist=true;Max Pool Size=300;Min Pool Size=10;Pooling=true;Application Name=DASH(10941vm3-IS)"

<font size="2">

 

</font>

 

providerName="System.Data.SqlClient" /><font size="2" color="#0000ff">

</

</font>

connectionStrings>

<font size="2" color="#0000ff">

</

</font>

configuration>

 

 My CS code ->

try

{ // code that cause exception in DAL }

 

<font size="2" color="#0000ff">

catch

</font>

(Exception  Ex)<font size="2">

{

</font>

if (ExceptionPolicy.HandleException(Ex, EXCEPTION_POLICY)) throw; }

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

<font size="2" color="#0000ff">

 

</font>

 

Jun 9, 2009 at 3:05 AM

Do you still get logs in the file and through email?  Could you try to set the Logging Errors and Warnings to use a FormattedEventLog TraceListener instead of the DatabaseTraceListener?  So you could see logs in the event viewer if there were any exception while logging to the database.

 

Sarah Urmeneta
Global Technology & Solutions
Avande, Inc.
entlib.support@avanade.com

Jun 9, 2009 at 8:24 AM

Hi, I am able to solve the problem.

 

Thanks,

Jimmy

Jun 10, 2009 at 11:52 AM
Edited Jun 10, 2009 at 11:58 AM

Currently I am logging all the exceptions to Logging database.

This Logging database I have created using LoggingDatabase.sql script file (located in the Source\Blocks\Logging\Src\DatabaseTraceListener\Scripts folder)

But now I want to log all the exceptions to my current database in use called Oz09. I have created req tables and stored procs in Oz09.

I have configured my app accordingly but it is not logging any exceptions to Oz09. I am able to receive exceptions through mails and logging in files.

I could see some difference in databse creataion script for both these databases as follows:( Diff. in RED)

Dose this anything to do with logging exceptions?

Oz09 ->

CREATE DATABASE [Oz09]  ON (NAME = N'Dash_Data', FILENAME = N'F:\devl\data\Oz09_Data.MDF' , SIZE = 53211, FILEGROWTH = 10%) LOG ON (NAME = N'Dash_Log', FILENAME = N'R:\devl\log\Oz09_Log.LDF' , SIZE = 3090, FILEGROWTH = 10%)
 COLLATE Latin1_General_BIN
GO

exec sp_dboption N'Oz09', N'autoclose', N'false'
GO

exec sp_dboption N'Oz09', N'bulkcopy', N'false'
GO

exec sp_dboption N'Oz09', N'trunc. log', N'true'
GO

exec sp_dboption N'Oz09', N'torn page detection', N'true'
GO

exec sp_dboption N'Oz09', N'read only', N'false'
GO

exec sp_dboption N'Oz09', N'dbo use', N'false'
GO

exec sp_dboption N'Oz09', N'single', N'false'
GO

exec sp_dboption N'Oz09', N'autoshrink', N'false'
GO

exec sp_dboption N'Oz09', N'ANSI null default', N'false'
GO

exec sp_dboption N'Oz09', N'recursive triggers', N'false'
GO

exec sp_dboption N'Oz09', N'ANSI nulls', N'false'
GO

exec sp_dboption N'Oz09', N'concat null yields null', N'false'
GO

exec sp_dboption N'Oz09', N'cursor close on commit', N'false'
GO

exec sp_dboption N'Oz09', N'default to local cursor', N'false'
GO

exec sp_dboption N'Oz09', N'quoted identifier', N'false'
GO

exec sp_dboption N'Oz09', N'ANSI warnings', N'false'
GO

exec sp_dboption N'Oz09', N'auto create statistics', N'false'
GO

exec sp_dboption N'Oz09', N'auto update statistics', N'false'
GO

if( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) )
 exec sp_dboption N'Oz09', N'db chaining', N'false'
GO

FOR Logging - >

CREATE DATABASE [Logging]  ON (NAME = N'Logging', FILENAME = N'f:\devl\MSSQL\data\Logging.mdf' , SIZE = 2, FILEGROWTH = 10%) LOG ON (NAME = N'Logging_log', FILENAME = N'f:\devl\MSSQL\data\Logging_log.LDF' , FILEGROWTH = 10%)
 COLLATE SQL_Latin1_General_CP1_CI_AS
GO

exec sp_dboption N'Logging', N'autoclose', N'false'
GO

exec sp_dboption N'Logging', N'bulkcopy', N'false'
GO

exec sp_dboption N'Logging', N'trunc. log', N'false'
GO

exec sp_dboption N'Logging', N'torn page detection', N'true'
GO

exec sp_dboption N'Logging', N'read only', N'false'
GO

exec sp_dboption N'Logging', N'dbo use', N'false'
GO

exec sp_dboption N'Logging', N'single', N'false'
GO

exec sp_dboption N'Logging', N'autoshrink', N'false'
GO

exec sp_dboption N'Logging', N'ANSI null default', N'false'
GO

exec sp_dboption N'Logging', N'recursive triggers', N'false'
GO

exec sp_dboption N'Logging', N'ANSI nulls', N'false'
GO

exec sp_dboption N'Logging', N'concat null yields null', N'false'
GO

exec sp_dboption N'Logging', N'cursor close on commit', N'false'
GO

exec sp_dboption N'Logging', N'default to local cursor', N'false'
GO

exec sp_dboption N'Logging', N'quoted identifier', N'false'
GO

exec sp_dboption N'Logging', N'ANSI warnings', N'false'
GO

exec sp_dboption N'Logging', N'auto create statistics', N'true'
GO

exec sp_dboption N'Logging', N'auto update statistics', N'true'
GO

if( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) )
 exec sp_dboption N'Logging', N'db chaining', N'false'
GO

Pleas guide me on this. If exceptions can be logged in Logging Db then why not it's getting logged in Oz09.

Both Db are on remote server. Server Instance name is POLO

 

 

 

 

 

Jun 10, 2009 at 12:18 PM

Hi,

Do you see any error logs in the event viewer regarding the failure of the DatabaseTraceListener? Also, please check if you have access and permission to the remote server.

Valiant Dudan
Global Technology & Solutions
Avande, Inc.
entlib.support@avanade.com

Jun 10, 2009 at 2:42 PM

Hi,

How to check the permission to the remote server? I can create database, stored proc, tables in remote server.

I am able to receive exception through e-mail and an entry is found in log file also but no entry in database.

eventviewer entry (in brief) is found as:

Message: Tracing to LogSource 'LoggingCategory' failed. Processing for other sources will continue. See summary information below for more information.

Should this problem persist, stop the service and check the configuration file(s) for possible error(s) in the configuration of the categories and sinks.

Exception Information Details:
======================================
Exception Type: System.Data.SqlClient.SqlException
Errors: System.Data.SqlClient.SqlErrorCollection
Class: 16
LineNumber: 0
Number: 8145
Procedure: WriteLog
Server: POLO
State: 2
Source: .Net SqlClient Data Provider
ErrorCode: -2146232060
Message: @eventID is not a parameter for procedure WriteLog.
Data: System.Collections.ListDictionaryInternal
TargetSite: Void OnError(System.Data.SqlClient.SqlException, Boolean)
HelpLink: NULL

 

If I use Logging database, exception is logged/publish to all 3 places i.e e-mail, log file and databse but if I use different database other than Logging;

the above situation will occur.

Jimmy

 

 

Jun 11, 2009 at 7:00 AM

If your application can already write to your database, then we can eliminate the probability of the permission issue.  I'm assuming you are performing database transactions in your app and you are able to insert records.  Is this correct?  Could you check the WriteLog stored procedure in Oz09 database?  Verify that it has the @eventId parameter because the error message states that there's no parameter of that name.  If it does, try running an sql profiler and see if the WriteLog stored procedure was actually executed and produced the error above.

 

Sarah Urmeneta
Global Technology & Solutions
Avande, Inc.
entlib.support@avanade.com

 

Jun 11, 2009 at 1:16 PM

I have sorted out the problem.

Stored Procs in Oz09 are CASE-SENSITIVE; and that's why it was not working. I have changed it now and app is logging exceptions to Oz09 perfectly.

Thanks for your timely help. It's a great learning exp. implementing EntLib 4.1 in my project.

 

Thanks,

Jimmy