BizTalk

I am heading off to London in a few short days to undertake the MS training for BizTalk Server 2006. I am hoping that my experiences will be a shade better than other times I have taken the official MS courses on new subjects.

The idea of BizTalk is very new to me and it will be interesting to see how it all fits together. I have heard all kinds of comments on BizTalk so it remains to be seen how this pans out for me.

I'll let you know how it goes.

Auditing tables with ntext fields

I need to track and audit changes to a field on a SQL Server 2000 database. Not difficult? I thought not. The first problem was with data typing, the field in question is an ntext field which is a problem.

Normally I would use:

CREATE TRIGGER xxx ON xxx FOR Insert/Update/Delete

This can't work in my scenario as neither the 'Inserted' nor the 'Deleted' tables will store the values of text, ntext or image fields in the case of a FOR trigger, otherwise known as an AFTER trigger as it takes place after the table change which triggered it.

Enter the INSTEAD OF trigger. This trigger bought in from SQL 2000 onwards supports the use of ntext, text and image fields in the 'deleted' and 'inserted' tables. However I have now discovered that you cannot use the values from these fields in local variables or sub-queries so I cannot get them out of the 'inserted' table and record them into my audit table.

The other problem with this approach is that the INSTEAD OF trigger (as the name rather handily implies) actually replaces the table change action which triggered it e.g. the original insert or update will be replaced by the trigger code. I could write code to re-perform the original action after my custom code, but this all sounds rather messy.

In short I am still stumped, auditing changes to an ntext field cannot be a rare occurence, and yet the answer evades me.

I should really say that I am not a SQL Server developer, but I am trying to debug a 3rd party application and suspect that there are problems when saving fields back to the database. Hence the need for an audit table.

Determining data types at runtime in VB.Net

I always forget how this should work. So here its is quick and easy...

To determine the data type of an object at runtime you can use the TypeOf() function.

For example :

If TypeOf(Control) is TextBox Then

'Some function here

End If