The saga of the Change Log

A recent conversation reminded me of the need for change logs within a database, to record when various change scripts were run. Creating such the required table is simple. A typical table for this consists of:

• Id - identity Integer primary key
• ChangeFileName - NVARCHAR(128) to hold the name of the file run.
• DateAdded - DateTime non-null with default value of getutcdate()
• Purpose - NVARCHAR(128)
• Rerunnable - Bit non-null default 0.

By good design of the table only two data values normally need to be supplied. Two stored procedures, one for inserting data and one to list in reverse sequence the log complete the database essentials. The complete implementation can be found in the CommonData solution at http://CommonData.CodePlex.Com

By including a call the add Change Log stored procedure, each script can log its name and purpose for posterity. The scripts that were applied to say the UAT system and their sequence of application can be readily identified for running on the Live system.

Formatting XML

XML is often produced as one continous string with no embedded CR/LF. To get it into human readable form, open it in visual studio, swap to another tab and back and click the format document button. The XML will then be nicely formatted!