Tim Rayburn's Blog - SQL Server - Connected People make Connected Systems

I was recently introduced to log4net by Ed Kisinger of EdSquared.com and absolutely love the project.  I've previously used to Logging Application Block inside Enterprise Manager for my logging, and would again if I was using the rest of EntLib, but log4net fills the wonderful point where you may not want to take on all of EntLib and still have excellent logging.

log4net abstracts the actually logging on messages from the location that a log will be stored.  As such it is easy to change from file logging, to UDP network logging to Database logging, all without touching the code which actually logs.  Fantastic idea!  The Appender model, as they call this, includes many excellent Appenders right out of the box.

In particular, I wanted to use to AdoNetAppender to log my messages to a database.  Easy enough, the simple example included in the AdoNetAppender documentation logs the most basic of information.  But as you dig deeper into what information log4net can store, you begin to realize how oversimplified this structure really is.

It would be a reasonable thing to say that I can sometimes be a bit of a perfectionist.  As such I spent this morning creating a database structure capable of holding all of the log4net information, properly normalized to the third normal form.  If you would like to be able to store any information available from log4net when logging to a database, then feel free to take this structure and adapt it to your needs.

The only personalized piece of this database at the moment is the inclusion of a column for storing a property called InstanceId.  As I work with BizTalk on a regular basis, and love Scott Colestock's BizTalk Deployment Framework, I included a column for the Orchestration instance id which his Serializable log4net extension includes.

Download Version 1.0 of log4net Database

I'm writing about this in the hopes that no one else ever has to suffer the way I just did trying to get a BizTalk install configured at a client site. 

The symptom was that it would start to configure, reach the BTS WMI phase of the process and suddenly end with an "Unspecified Error".  If you review the log file you would find error 80004005 was returned from btswmi.cpp and absolutely no other useful information.

The cause of this problem is that NetBIOS is being used to determine if your computer is already in a BizTalk Group.  NetBIOS only supports machine names of LESS than 15 characters.

The solution is to rename your machine to something with less than 15 characters.  This will of course take a Domain Admin in a domain situation.

The gotcha is if like me you run off an immediately rename, you can be left with orphaned jobs on your SQL Server if it is on the same box.  The sys_jobs table contains the machine name.  Before you rename, be sure to remove any BizTalk jobs which were created or if you forget like I did, open Query Analyzer and update sys_jobs to have the new correct computer name.  If you do not update sys_jobs Enterprise Manager will not let you delete the jobs, and the BizTalk configure will now fail even earlier in the process.

The other gotcha is that your BizTalk Administrators Group will not longer have access to SQL Server because it will be known by it's fully qualified name <machine>/BizTalk Administrators and so you will need to give this group rights to the SQL Server again.

I had reason to help a client the other day automate the transmission of data from a table they controlled in their system to my company on a daily basis in order to allow us to use that data in performing certain operations for them.  Initially they were going to send a tab delimited file, and then I suggested they send XML.  They indicated that they weren't comfortable formatting XML and I said ... “Have SQL Server handle that for you.”  What resulted was an education for them on how to retrieve XML from SQL Server and the following code snippet:

This function was originally found at the following address:

http://msdn.microsoft.com/library/en-us/dnsqlmag01/html/treatyourselfListing_01.txt

But strikes me as so utterly useful that I'm archiving here as an article just so that I have a copy when next I find myself in a situation where I need it.

CREATE FUNCTION fn_Split(@sText varchar(8000), @sDelim varchar(20) = ' ')
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))
AS
BEGIN
DECLARE @idx smallint,
@value varchar(8000),
@bcontinue bit,
@iStrike smallint,
@iDelimlength tinyint

IF @sDelim = 'Space'
BEGIN
SET @sDelim = ' '
END

SET @idx = 0
SET @sText = LTrim(RTrim(@sText))
SET @iDelimlength = DATALENGTH(@sDelim)
SET @bcontinue = 1

IF NOT ((@iDelimlength = 0) or (@sDelim = 'Empty'))
BEGIN
WHILE @bcontinue = 1
BEGIN

--If you can find the delimiter in the text, retrieve the first element and
--insert it with its index into the return table.

IF CHARINDEX(@sDelim, @sText)>0
BEGIN
SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END

--Trim the element and its delimiter from the front of the string.
--Increment the index and loop.
SET @iStrike = DATALENGTH(@value) + @iDelimlength
SET @idx = @idx + 1
SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))

END
ELSE
BEGIN
--If you can’t find the delimiter in the text, @sText is the last value in
--@retArray.
SET @value = @sText
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
--Exit the WHILE loop.
SET @bcontinue = 0
END
END
END
ELSE
BEGIN
WHILE @bcontinue=1
BEGIN
--If the delimiter is an empty string, check for remaining text
--instead of a delimiter. Insert the first character into the
--retArray table. Trim the character from the front of the string.
--Increment the index and loop.
IF DATALENGTH(@sText)>1
BEGIN
SET @value = SUBSTRING(@sText,1,1)
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
SET @idx = @idx+1
SET @sText = SUBSTRING(@sText,2,DATALENGTH(@sText)-1)

END
ELSE
BEGIN
--One character remains.
--Insert the character, and exit the WHILE loop.
INSERT @retArray (idx, value)
VALUES (@idx, @sText)
SET @bcontinue = 0
END
END

END

RETURN
END