Tuesday, May 31, 2005

Adding VSS shortcuts to SQL Query Analyzer

Like many cf programmers, I've gotten into the habit of doing almost all of my queries in SQL Stored Procedures. It helps protect from SQL Injection attacks because you are forced to parameterize all of your passed in values. Plus for some reason, I find cfsqlparam to be one of the ugliest parts of the cf language. I know it's a little irrational, but it just bugs me. Cfprocparam seems so much cleaner.

If you wind up working this way, you soon find out how important it is to manage your queries in .sql scripts. Once you get into the habit of storing your sql files, its natural to want to put them under source control.

I've been carrying this process around for a few years. Query Analyzer lets you add items to it's tools menu. It will also let you pass a few parameters along to the commands you can execute.

If you open up Query Analyzer and click tools|customize|tools, you can add items to the menu.

Here are the items I generally add.

Anything in bold, you will need to replace with your own information.

Menu ContentsCommandArgumentsInitial Directory
Set Project Pathss.execp $/myProject/-yusername,password
Set Working Folderss.exeworkfold $(FileDir) -yusername,password
Add Current Filess.exeadd $(FilePath) -yusername,password$(FileDir)
Check Outss.execheckout $(FileName)$(FileExt) -C -yusername,password$(FileDir)
Check Inss.execheckin $(FileName)$(FileExt) -yusername,password$(FileDir)
Undo Checkoutss.exeundocheckout $(FileName)$(FileExt) -yusername,password$(FileDir)
Getss.exeGet $(FileName)$(FileExt) -yusername,password$(FileDir)

You'll have to add a small batch file to use the diff utility. If you try to call it the same way the other commands are being called, your command window will close as soon as the program is done diffing. You won't get a chance to actually look at the files.

Here is the content of my diff.bat file:

ss diff %1 -yusername,password

Make sure you add the path to your diff.bat file to your environment variables, otherwise you will need to specify the complete path to the file in the command line in Query Analyzer.

If you are used to the process that Dreamweaver or CFStudio uses to checkin/checkout files to VSS, this will take a little practice. Query Analyzer doesn't keep an open handle on the files you open. You will need to make sure you manually save a file before you check it in. Otherwise, the unedited file is checked back in, doing nothing. Don't worry, it starts to feel pretty natural after you do it a while.

You also need to change the project and working path entries when you decide to work on a different project. If you start seeing files checked into the wrong project, chances are you need to edit those entries.

Many people find it easier just to use the VSS GUI instead of this. I can certainly understand that. It's a little safer. I find that I tend to like this method a little better because it's one less program I have to keep running and switching between.

It looks like MS is adding some nice source control hooks into Sql 2005. You can add different source control providers through MMC plugins. Too bad they screwed up the remote access portion of VSS. Maybe they will change it before it's actually released, but I wouldn't hold my breath.

NOTE: you have to save your sql file before you can do a checkin. Normally this won't be a problem because that is probably your normal workflow. If you attempt to do a checkin and you don't get a command window asking for a comment, most likely your checkin didn't work. Make sure you have saved the file and run through the "Set project path" and "Set working folder" options again before you try to check in.


  1. "Set Working File" command's arguments are:

    ss.exe workfold c:\inetpub\wwwroot\myproject

  2. Oops. Good catch. I'll make the change above just in case somebody decides to try it.

  3. Great work

    found it most usefull