Templates and teamwork

Templates and teamwork

Introduction

Back in April 2003 Greg Larson wrote what was pretty much an all-encompassing article on using query analyser templates within SQL Query Analyser.

I use SQL Management Studio so I am intending this article to be a brief addendum to Greg's work therefore I recommend that you read his original before proceeding.

Differences between SQL Query Analyser templates and SQL Management templates

Thankfully the core functionality remains the same. Predictably there are many more templates in SQL Management Studio. The other differences are minor as shown below

One major annoyance that may or may not affect you is the way that SQLMS reacts to XML fragments in templates. Basically it incorrectly interprets the XML fragment as being a template place marker and corrupts the resulting code.

Team templates

If you work in a team then the chances are that anything worth putting in a template is worth sharing. There are a few things you need to think about if you are going to do this.

Storage Structure for templates

Using the View menu as shown below or CTRL + T bring up the template explorer.

As you will see the template folders, of which there are a great deal, are listed in alphabetical order.

At first alphabetical order seems logical but if you find yourself using the "Table" and "Stored Procedure" templates a lot it quickly becomes tiresome to keep having to scroll down the list.

I have already said that the SQL Management Studio templates are stored in the user profile so on my box they are stored under

C:\Documents and Settings\David.Poole\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\Templates\Sql\

To get the "0. Favourites" folder at the top of the template I have simply created a sub folder

C:\Documents and Settings\David.Poole\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\Templates\Sql\0. Favourites\

By numbering our folders we are ensuring that our folders go to the top of the list. As ordering is alphabetic you need to remember that 1, 10 and 100 will appear before 2, 20, 200 in the list so if you are going to have more than 10 folders you are going to have to pad them with prefixing zeros. I suspect that if you reach the stage where you need to prefix then you need to go back and rethink your structuring strategy.

The choice of favourites is a matter of personal choice so I recommend that all users set up a "0. Favourites" folder. This should not be overwritten from the master source and should remain specific to the user.

As to any other folders you may create for the team, if you are going to create for the team then you need to speak to the team to decide what is going to be useful and what precedence it should take.

Peer review and checking templates

I've got snippets of code that I use day to day. They are really a bag of useful tricks rather than something I would deploy to my colleagues. I know what the weaknesses are in my snippets and how they have to be adjusted to suit different scenarios but I shouldn't assume that my colleagues will think or work in the same way.

All this is a long winded way of saying that shared code needs to be clear, unambiguous, bug free and easy to understand. The best way of achieving this is to get at least one member of your team to peer review it. It is going to be used by the team so the team need to be comfortable with it.

Deployment method

Shared Template Repository

First of all you need a shared repository for templates such as a mapped network drive. For the sake of argument let us assume that we have a T: drive with a directory structure as follows:

T:\Tools\SQL Management Studio\

Deployment Windows Command/Batch File

The next step is to set up a Windows command/batch file to copy the contents of this folder down to your team's local drives. I call this DownloadManagementStudioTemplates.cmd and this will contain a single XCOPY command

XCOPY "T:\Tools\SQL Management Studio" "%USERPROFILE%\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\Templates\Sql" /i/e/c/y

The use of the %USERPROFILE% environment variable ensures that the templates will copy to the correct directory on any of your team's PCs.

The XCOPY switches used are as follows

Running the Windows Command/Batch File

For my own use I put the DownloadManagementStudioTemplates.cmd into my \Start Menu\Programs\Startup folder. This ensures that whenever I log on I get the most up-to-date set of templates.

To make things easier I set up a second Windows command/batch file called InstallSQLTemplates.cmd which itself contains a single XCOPY command as follows

XCOPY "T:\Tools\DownloadManagementStudioTemplates.cmd" "%USERPROFILE%\Start Menu\Programs\Startup" /c/y

Refreshing the templates

Whenever a team member logs on they will get the most up-to-date set of shared templates. When they go into SQL Management Studio those templates will be available to them.

You need to be aware that SQL Management Studio grabs a list of templates when it starts up. It does not have a refresh facility should the contents of your local template folder be changed while it is running. If you do update the template folder then you will have to shut down SQL Management Studio and restart it.

Using the Technique With Red-Gate SQL Prompt Snippets

The basics

Red-Gate SQL Prompt provides SQL intellisense for SQL Query Analyser and SQL Management Studio.

Useful though this is a much more interesting feature of SQL Prompt is its "Snippets" feature. A snippet being a piece of boiler plate text that you wish to attach to a macro. For example, typing

scf

SELECT COUNT(*) FROM

The snippets supplied with the product are quite short but as the package provides the facility to add/amend these items there is no reason why larger blocks of text cannot be attached to key combinations.

Following this train of thought and what SQL Prompt snippets can provide is a sub-templating facility.

At this point I should like to point out that sub-templating can be achieved wihtin SQL Management Studio itself using the following technique

1. Select a template in the template explorer
2. Press CTRL + C or right-click and choose COPY
3. In your query window click where you want to insert the contents of the template
4. Press CTRL + V or right-click and choose PASTE

Like SQL Management Studio templates these snippets are actually stored in an XML file within the user profile under

\Local Settings\Application Data\Red Gate\SQL Prompt 3

If your team uses Red-Gate SQL Prompt then exactly the same technique can be used to deploy a master snippets file down to the teams local drive

XCOPY "T:\Tools\SQLPrompt\SQL Prompt 3 Snippets.xml" "%USERPROFILE%\Local Settings\Application Data\Red Gate\SQL Prompt 3" /c/y

Additional Considerations for SQL Prompt

SQL Management Studio Templates are separate files where as SQL Prompt snippets are held in a single file. This means that all team members will have exactly the same snippets with no allowances for deviations.

Deploying the snippets is not the issue, deciding what should go in the snippets is the tricky part.

A further consideration is how to approach the development of snippets. Realistically we are talking about very small fragments of code but if you have a nightly download from a central source you need to remember that this is going to happen. You don't want to have created several snippets, forgot to copy them to the central repository and had them overwritten when you next log in.

Conclusion

Sharing templates within a team environment should give two advantages

• Consistency
• Reduced workload

I have shown a method that works with SQL Management Studio templates and also Red-Gate SQL Prompt. I know that the same technique will also work to share documentation templates in Innovasys DocumentX.

Stand-a-lone tools are all very well but they become far more useful if they have the ability to share information between users. It is always worth having a look at how your chosen applications work and whether they can share information either directly or indirectly.