Thank you Bob. I've been feeling a little ganged up on till you and Phil Factor came along. I knew that there must be someone out there who had or will encounter similar circumstances as I have and may find this article useful. Over time I've grown skeptical of that assumption, but your feedback had really made it worth my while. I am very glad you enjoyed the article and found it useful and I appreciate you posting your elaboration on the code.Thank you very muchK

I unfortunately have ntext fields in the table(s) that need to be audited and I've been trying to find a way to get around it.

My attempt (I can't call it a solution because it doesn't work) is to try to just insert the fields that aren't ntext, text or image into a temp table. I do this by getting the names of the fields for the table being audited from the syscolumns table and put them in a variable.

My code looks like this:

CREATE TABLE #TableCache ( columnName varchar(400))

INSERT INTO #TableCache SELECT '[' + sc.name + ']' as columnName FROM sysobjects as so JOIN syscolumns as sc ON so.id = sc.id WHERE so.xtype = 'U' AND sc.xtype NOT IN ( 99, 35, 34) AND so.name = @TableName AND sc.name not in ('Created', 'Modified', 'RowVersion')

-- loop over the cursor-- and get a list of the columns in the table that are not ntext, text or imageOPEN tableColumnFETCH NEXT FROM tableColumn INTO @columnNameWHILE @@FETCH_STATUS = 0BEGIN if len(@FieldList) = 0 begin SET @FieldList = '[' + @columnName + ']' end else begin SET @FieldList = @FieldList + ', [' + @ColumnName + ']' end -- next row, please FETCH NEXT FROM tableColumn INTO @columnNameEND

Just to add my 2 cents. I also have been down this path using loops and what not to audit data into a generic set of tables. However, with the advent of the Service Broker, I now do all auditing asynchronously and I can handle any data type thrown at the database (including ntext, text, image, etc).

The first thing to do is setup the Audit database. This database will have one Procedure which will "receive" the incoming audit "messages" (i.e. incoming data) and will insert the incoming Xml into the generic auditing table(s).

The 2nd thing I did was I came up with a generic Trigger which could be scripted onto every table I wanted to audit. The trigger would selected all the data being updated / deleted (I didn't worry about inserts since having a CreatedDate column on all the rows would already give me that audit) and use the FOR XML clause to stuff the updated rows into Xml. In SQL 2005 the FOR XML clause has been greatly enhanced which reduced the amount of logic needed to generate a generic xml structure. Needless to say, inserting massing amounts of data into Xml is much faster than loops.

Once I had the Xml (2 xml variables, one for the "inserted" and optionally one for the "deleted" virtual tables), I passed the data off to a generic procedure which simply took the data and passed it off through the Service Broker messages I had setup. Since the SB is async, this did not slow down the OLTP data that needed to be inserted / deleted.

Please note that a SB implementation can be setup where a database can communicate with itself asynchronously instead of sending data off to another database or server instance altogether. I just choose to have a separate database so all of our application databases could be audited in a generic way without changing anything or complicated setup within each DB.