THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a data mangler in London working for Dunnhumby

TFS Log Substitution Policy

I recently joined a new client at which Team Foundation Server (TFS) is being used as the source control repository and Continuous Integration (CI) server (I think these things collectively gets referred to as Application Lifecycle Management or ALM for short). I'm fairly ambivalent as to which ALM tools gets used on my projects (in the past year I have used various combinations of TeamCity, Jenkins, Git, Github, Subversion and Jira and haven't had any particular problem with any of them) however returning to TFS did give me an opportunity to try out a utility that I have been intending to look at for a while - $log$ / Keyword Substitution / Expansion Check-In Policy (in the interests of brevity let's call it Log Substitution).

Log Substitution  is a TFS Check-in policy that will look for and replace the string "$log$" with information about that check-in; what that means in practise for us T-SQL developers is that you can include the check-in history of a stored procedure within the stored procedure definition. Here's an example of what that looks like:

CREATE PROCEDURE [UKTax].[csp_EntityLoad]
@pProcessDetailID           SMALLINT    
,  @pDomain                    VARCHAR(64)
@pUserName                  VARCHAR(255)
@pRowCount                  INTEGER         = 0                  OUTPUT
       Previous Revision 3104 2011/09/09 14:42:36  Sup_ThomsonJ
         refactoring. Eliminated duplicate code - now adhering to DRY
       Previous Revision 2974 2011/09/08 17:13:43  Sup_ThomsonJ
         Transaction needs to be committed
       Previous Revision 2898 2011/09/01 09:58:31  Sup_ThomsonJ
         Adding $log$ directive for substitution policy
--code goes here etc...

 As you can see we have a comments section that contains, for each check-in, the following information:

  • The most recent revision number for this file prior to the check-in
  • Date and time
  • The person checking-in
  • The check-in comment

I asked the various communities within the company what they thought of this and was interested to discover that the .Net developers seemed dead against it - they didn't see the point in copying the check-in information to a different place - whereas the SQL developers were all for it; their reasoning was that it is useful to be able to look at a stored procedure in a production environment and see who to blame when it goes wrong. If you think about it there is no analogy to stored procedures for .Net developers because .Net source code does not get deployed, the compiled object code does, so perhaps its not surprising that there is a dichotomy of opinion here.

I should point out that there is a downside to TFS Check-in policies - they are enforced on the client rather than on the server (which is stupid) and moreover this means that a client install is required. This seems like a rather large limitation to me and although the problem can be alleviated somewhat by the use of TFS Powertools that, again, requires those Powertools to be installed everywhere beforehand (why the TFS Powertools don't just get delivered with Team Explorer is beyond me). Anyway, if you can get around those little nuances Log Substitution can be a useful little utility and given that it went down so well with my fellow SQL developers here I thought it might be worth sharing with all of you. Let me know if you have any opinion on Log Substitution, especially if you have installed it and used it yourself.






Published Wednesday, September 14, 2011 2:36 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Rafi said:

Hi Jamie,

Though I'm now a T-SQL and SQL Server developer (started as a C developer for 11 years ago), I agree with the .NET ppl. The thing is, that when this information is injected by TFS in the SP code, it can after that be edited by someone manually, outside of the TFS context, breaking the week chain totally.

One should not allow the audit tracks to be offline, and edible, as it will degrade its authenticity.

So it's nice to have, sure thing it's cool for getting the answer fast etc., but if you will anyhow still need to verify the info with the TFS, than it's not really saving you the time and effort.

Single version of the truth, is still a very important element of every information system.  

September 15, 2011 2:55 AM

jamiet said:

Hi Rafi,

I'm an avid proponent of DRY so you know what, on that principal alone I can't really disagree with you. Maybe I need to have a word with some of my colleagues... :)


September 15, 2011 3:33 AM

Kristian Wedberg said:

I'd put good money on +80% of SQL developers using source control systems are (usually manually) also adding change comments to the sproc - I know all my TFS projects have, and the majority of the SQL developers have preferred keeping it that way.

I'm guessing & hoping this will change over time...


September 22, 2011 4:38 PM

Jack Corbett said:

I always put change comments in the header of any stored procedure, and when they are in source control as well, I normally put the same comment in it on check-in.  Having to do it just once is nice.

Yes, they can be edited outside of source control, but you can always define the check-in comments to be the "master" comments.

September 23, 2011 11:01 AM

Leave a Comment


This Blog


Privacy Statement