Introduction

Database source code control is a proven technique for maintaining system stability, but it is all too often overlooked or treated as an afterthought. Although database backups provide a means of recovering database source code found in stored procedures, triggers, and user defined functions, they aren’t suitable for providing true source code control. Effective source code control is more than just having a repository for source code. Microsoft’s Visual SourceSafe (VSS) provides the version control features needed for enterprise-level code maintenance activities. To get the most out of SourceSafe, you have to know what it can do for you. Some underutilized, but quite useful, features in SourceSafe are keyword expansion and keyword comments. It’s not immediately obvious how to take advantage of these features, so this article shows you how. You will also be provided some suggestions on best practices using VSS.

Code Before and After Source Control

SourceSafe has several keywords available to help you keep track of changes to code. We will start with an incorrectly written stored procedure not under VSS version control. It contains a logic error in which it adds instead of multiplies when updating the prices.

CREATE PROCEDURE dbo.UpdatePrices
(
  @PriceMultiplier decimal(10,6)
)
AS
  IF @PriceMultiplier IS NULL
    RETURN -1 -- invalid input, return a bad status
  ELSE
    BEGIN
      UPDATE Products
      SET UnitPrice = UnitPrice + @PriceMultiplier

      RETURN 0 -- return a successful completion status
    END

Next, the following actions are undertaken to simulate a real-world scenario:

1. Add the following line to the stored procedure:

--$History: $

2. Save the stored procedure as UpdatePrices.sql.

3. Add UpdatePrices.sql to VSS.

4. Discover the logic error.

5. Check UpdatePrices.sql out of VSS.

6. Open UpdatePrices.sql in the Query Analyzer.

7. Change the CREATE to ALTER.

8. Fix the logic error and update the procedure in the Query Analyzer.

9. Save the updated procedure to UpdatePrices.sql.

10. Check UpdatePrices.sql into VSS.

11. Open UpdatePrices.sql in the Query Analyzer to see what VSS did to the file contents.

Here is the outcome of the steps outlined above:

 ALTER            PROCEDURE
           dbo.UpdatePrices
           
--$History: UpdatePrices.sql $

--
--*****************  Version 2  *****************
--User: Dbazine     Date: 11/22/03  Time: 6:28p
--Updated in $/Stored Procedures
--Changed + sign to * sign to fix logic error.
--
--*****************  Version 1  *****************
--User: Dbazine     Date: 11/22/03  Time: 6:26p
--Created in $/Stored Procedures
--Procedure to update prices.
(  
  @PriceMultiplier decimal(10,6))
AS  
  IF @PriceMultiplier IS NULL    
    RETURN -1  -- invalid input, return a bad status  
  ELSE    
    BEGIN      
      UPDATE Products      
      SET UnitPrice = UnitPrice * @PriceMultiplier      
      RETURN 0  -- return a successful completion status    
    END

Notice that although there are 11 new lines of inline comments in the stored procedure, we only entered one inline comment: the line containing the $History: $ keyword. The keyword was automatically expanded and caused VSS to add the 10 lines that followed it.

Whenever VSS keywords are placed in any file that must be interpreted, parsed, or executed, the keywords must be placed within comments.

Configuring the SourceSafe Server

Keyword expansion is not enabled by default. It may be enabled by editing the srcsafe.ini file or using the Visual SourceSafe Administrator. The default location of srcsafe.ini is C:\Program Files\Microsoft Visual Studio\VSS\srcsafe.ini. To enable keyword expansion by using the Visual SourceSafe Administrator, go to the menubar and select Tools, then Options.

On the SourceSafe Options dialog box, go to the General tab, and enter *.SQL underneath Expand keywords in files of type. Click OK.

The Visual SourceSafe Administrator has just added the following line to the srcsafe.ini file before any bracketed sections (e.g., [Keyword Comments] shown below):

Keyword_Masks = *.SQL

If you prefer, you can make the addition manually. That might be more convenient considering that the next change to the srcsafe.ini file must be made manually.

VSS must be configured to have keyword comments enabled. When VSS expands a keyword, it inserts text into the file. In a stored procedure, the inserted text would cause errors that would prevent the stored procedure from being saved. By enabling keyword comments in VSS, the lines of text are inserted as SQL comments. This is accomplished by making the following addition to the srcsafe.ini file:

[Keyword Comments]
*.SQL = "--"

Another approach to making each expanded line a comment is to make each line a block comment:

[Keyword Comments]
*.SQL = "/*","*/"

If for some reason you don’t want the expanded lines of text to have comment characters, leave the Keyword Comments section out of the srcsafe.ini file. You’ll have to change your keyword syntax to the following:

/*
$History: $
*/

This alternative approach gives the following result:

ALTER PROCEDURE dbo.UpdatePrices
/*
$History: UpdatePrices.sql $

***************** Version 2 *****************
User: Dbazine     Date: 11/22/03  Time: 8:17p
Updated in $/Stored Procedures
Changed + sign to * sign to fix logic error.

***************** Version 1 *****************
User: Dbazine     Date: 11/22/03  Time: 8:14p
Created in $/Stored Procedures
Procedure to update prices.
*/
(
  @PriceMultiplier decimal(10,6)
)
AS
  IF @PriceMultiplier IS NULL
    RETURN -1 -- invalid input, return a bad status
  ELSE
    BEGIN
      UPDATE Products
      SET UnitPrice = UnitPrice * @PriceMultiplier

      RETURN 0 -- return a successful completion status
    END

Best Practices

As you have seen, VSS is a useful tool with helpful features, but it is only as effective as users allow it to be. For VSS to “automagically” insert explanations of what changes have been made, the users must provide them in the first place! All VSS users must understand the importance of providing clear explanations of changes to the code. When a file is checked out of VSS, the user should provide an explanation as shown below:

After making the changes, when the file is checked in, the Check Out comment appears in the Check In dialog box. Users should take the opportunity to add comments explaining what changes were made.

Users who do not cooperate by entering comments can be held accountable because their VSS usernames will be recorded. A quick review of the history shows who isn’t providing explanations of why changes are made.

Before making changes into VSS, it’s prudent to take advantage of VSS’s difference utility. It provides a clear, visual presentation of the changes. Mistakes can be made when changing code. It’s a good idea to review all changes and make sure that they were intentional.

The pending changes appear like this:

Only after all changes have been confirmed as appropriate should the changed code be checked into VSS. Once the changed code is safely stored in VSS, it should be applied to the database by using osql or the SQL Query Analyzer. The advantage to this approach is that all of the SourceSafe comments get saved in the database when the script file is executed. Later on, when the database code needs to be reviewed, it includes the SourceSafe comments. The comments will provide a historical perspective about the code which is very useful in determining why the code is in its current state and who has been modifying it.

If you are versioning Oracle code, keep in mind that Oracle strips out comments under certain circumstances. For example, when creating a view, comments before the SELECT statement will be removed. Comments placed after the SELECT statement will be preserved.

The SQL Query Analyzer isn’t the only way to create and edit stored procedures. The Visual Studio.NET (VS.NET) IDE is fully integrated with VSS. Using VS.NET integrated with VSS simplifies the housekeeping details of working with source code files.

Conclusion

It only takes a few minutes to configure Visual SourceSafe to support keyword expansion and keyword comments. Although file check-in and check-out is not integrated into the Query Analyzer, the advantages of version control far outweigh the minor inconveniences of saving SQL Server source code to files that can be checked into VSS. Being able to maintain a history of changes is invaluable in understanding the root causes of code related problems. It’s also wise to version administrative scripts found in cmd and bat files. If you can save something to a file, you can place it under version control.

--

John Paul Cook is a database and systems architect in Houston, Texas. His primary focus is helping large enterprise customers succeed with SQL Server, Oracle, and the .NET framework.