Log4TSql Files

Log4TSql is a simple, open source logging and exception handling framework for SQL2005+.

It is designed to simplify the process of collecting and persisting details about each caught exception
and also provide functionality to facillitate the detailed logging of complex stored procedures.

To get the best out of Log4TSql, it is recommended that you install the HOWTO sprocs and review them,
starting with HOWTO_ExceptionHandler_SimpleExample.


Installation Instructions
=====================================================================================================================

Log4TSql.deploy.sql
=============================
Creates a schema called [log4] in the currently selected database and creates all objects required for the
Log4TSql in that schema.


Log4TSql.howto.sql
=============================
Adds some additional stored procedures to the [log4] schema which can be used to learn how to get the best
out ofLog 4TSql.  Examples range from the really simple to the more complex with tips for use based on
real-world usage of this framework.


Log4TSq.remove.sql
=============================
Removes all traces of Log4TSql including dropping the [log4] schema


Core Functions
=====================================================================================================================

[log4].[ExceptionHandler]
=============================
Captures all available error information within a CATCH block, stores this in the [log4].[Exception] table and also
exposes those values as OUTPUT parameters.  Allows the caller to specify an error context which is also stored.

The method signature looks like this (no parameters are mandatory):

  @ErrorContext		nvarchar	(  512 )	= NULL
, @DatabaseName		nvarchar	(  128 )	= NULL	OUT
, @ErrorProcedure	nvarchar	(  128 )	= NULL	OUT
, @ErrorNumber		int						= NULL	OUT
, @ErrorSeverity	int						= NULL	OUT
, @ErrorState		int						= NULL	OUT
, @ErrorLine		int						= NULL	OUT
, @ErrorMessage		nvarchar	( 4000 )	= NULL	OUT
, @ReturnMessage	nvarchar	( 1000 )	= NULL	OUT
, @ExceptionId		int						= NULL	OUT


[log4].[ExceptionReader]
=============================
Allows the contents of [log4].[Exception] to be easily reviewed.
Results are in reverse date order, i.e. most recent at the top.
All parameters are optional and can be used as follows:

EXEC log4.ExceptionReader
		  @StartDate			= '20110906 00:00:00' -- Defaults to 00:00:00 7 days ago
		, @EndDate				= NULL -- Defaults to 23:59:59 today
		, @TimeZoneOffset		= 7 -- Number of hours to add/subtract to search criteria and results on
		, @ErrorProcedure		= NULL -- Name or part of module name to filter
		, @ProcedureSearchType	= NULL -- 1 = begins with; 2 = ends with (so 3 = contains); 4 = exact match
		, @ErrorMessage			= NULL -- Error message text (or part of) to filter on
		, @MessageSearchType	= NULL -- 1 = begins with;, 2 = ends with (so 3 = contains); 4 = exact match
		, @ResultSetSize		= 100 -- Defaults to last 100 errors


[log4].[JournalWriter]
=============================
Logging module allows progress and completion info to be captured from within any stored procedure

  @FunctionName			varchar		(  256 )
, @MessageText			varchar		(  512 )
, @ExtraInfo			varchar		(  max )	= NULL
, @DatabaseName			nvarchar	(  128 )	= NULL
, @Task					nvarchar	(  128 )	= NULL
, @StepInFunction		varchar		(  128 )	= NULL
, @Severity				smallint				= NULL
, @ExceptionId			int						= NULL
, @JournalId			int						= NULL OUT


[log4].[JournalReader]
=============================
Gives access to the contents of [log4].[Journal]

EXEC log4.JournalReader
		  @StartDate			= '20110906 00:00:00' -- Defaults to 00:00:00 7 days ago
		, @EndDate				= NULL -- Defaults to 23:59:59 today
		, @TimeZoneOffset		= 7 -- Number of hours to add/subtract to search criteria and results on
		, @FunctionName			= NULL -- Name or part of module name to filter on
		, @FunctionSearchType	= NULL -- 1 = begins with; 2 = ends with (so 3 = contains); 4 = exact match
		, @MessageText			= NULL -- Message text (or part of) to filter on
		, @MessageSearchType	= NULL -- 1 = begins with; 2 = ends with (so 3 = contains); 4 = exact match
		, @SeverityBitMask		= 8191 -- Severity bit mask filter SELECT * FROm log4.Severity for possible flags (ALL 8191)
		, @ResultSetSize		= 100 -- Defaults to last 100 entries


[log4].[JournalPrinter]
=============================
To drill into a particular Journal entry, pass the Journal ID of interest to log4.JournalPrinter

EXEC log4.JournalPrinter @JournalId = 4727812;



[log4].[JournalCleanup]
=============================
Deletes entries from Exception and Journal older than the specified number of days.

Has the following inputs:

  @DaysToKeepJournal            int
, @DaysToKeepException			int
 

 
=====================================================================================================================


<CopyrightNotice>
=====================================================================================================================
(C) Copyright 2006-12 data-centric solutions ltd. (http://log4tsql.sourceforge.net/)

This library is free software; you can redistribute it and/or modify it under the terms of the
GNU Lesser General Public License as published by the Free Software Foundation (www.fsf.org);
either version 3.0 of the License, or (at your option) any later version.

This library is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY;
without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
See the GNU Lesser General Public License for more details.

You should have received a copy of the GNU Lesser General Public License along with this
library; if not, you can find it at http://www.opensource.org/licenses/lgpl-3.0.html
or http://www.gnu.org/licenses/lgpl.html
=====================================================================================================================
</CopyrightNotice>



<ReleaseNotes>
=====================================================================================================================
ChangeDate      Version     Narrative
============    =======     =========================================================================================
01-DEC-2006     v0.0.1      Initial release with ExceptionHandler, ExceptionReader, JournalWriter and JournalReader
------------    -------     -----------------------------------------------------------------------------------------
16-FEB-2007     v0.0.2      Added JournalCleanup procedure and FormatElapsedTime function
------------    -------     -----------------------------------------------------------------------------------------
15-APR-2008     v0.0.3      Added SessionInfoOutput - called by ExceptionHandler and JournalWriter
							Added GetJournalControl function
------------    -------     -----------------------------------------------------------------------------------------
16-SEP-2010     v0.0.4      Added HOWTO sprocs as templates/user guides
------------    -------     -----------------------------------------------------------------------------------------
03-MAY-2011     v0.0.5      Added support for JournalDetail table - removing ExtraInfo column from Journal table
							Added @TimeZoneOffset parameter to Readers for ease of use in other timezones
							Added JournalPrinter
------------    -------     -----------------------------------------------------------------------------------------
28-AUG-2011     v0.0.6      Added support for ExceptionId and Task columns on Journal table
------------    -------     -----------------------------------------------------------------------------------------
29-AUG-2011     v0.0.7      Updated JournalCleanup to ensure that Exception deleted date is greater than Journal
							delete date
------------    -------     -----------------------------------------------------------------------------------------
05-NOV-2011     v0.0.8      Added log4.PrintString (which is SQL2005 compatible)
							JournalPrinter now calls log4.PrintString
------------    -------     -----------------------------------------------------------------------------------------
12-JAN-2012     v0.0.9      Fixed object references in HOWTO sprocs
------------    -------     -----------------------------------------------------------------------------------------
13-MAR-2012     v0.0.10     Fixed backwards-compatability issue with @LineFeedPos in log4.PrintString
------------    -------     -----------------------------------------------------------------------------------------

=====================================================================================================================

=====================================================================================================================
</ReleaseNotes>