From the Coalface - 3 - Work as hard as you can to be as lazy as you can!

Posted by TATWORTH on Geeks with Blogs See other posts from Geeks with Blogs or by TATWORTH
Published on Mon, 14 Jun 2010 18:31:56 GMT Indexed on 2010/06/14 19:42 UTC
Read the original article Hit count: 171

Filed under:

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!

© Geeks with Blogs or respective owner