VS.NET SQL Projects

On my current project, we’ve been using the SQL Project that’s included with VS.NET. I did this with some hesitation. I wanted the scripts to be integrated with the IDE, but I’ve never had any luck with managing SQL scripts. I had 3 issues:

1. The command file that VS.NET generates never executed my scripts in the correct order. If you execute a stored procedure that has a column that doesn’t exist, you’ll get an error
   
2. How do you manage scripts that should only be run once? You don’t want to add the same field twice.


3. Keeping code synchronized with the database changes

After some research and trial and error we fixed the first issue. I didn’t realize (since it isn’t documented anywhere that I could find) that the command file uses the script’s file extension to determine the order of scripts in the .cmd file. So it is important to name your files with the correct file extension.

The “Rules”:

1. Table files with the .tab file extension
   
2. Stored procedures with .prc
   
3. Finally .sql files for all data modifications

The second problem, we fixed with a simple rule: “All scripts must be able to be run multiple times”. What this rule means is that if a script is going to add a column to the table, the script needs to check if the columns already exist before running. This rule also makes it possible to integrate our SQL changes into our nightly build process. This is great because we validate our scripts every night; schema changes, data, and security settings.

Third, we simply created a folder for each version of the software. Under each version folder we added a folder for each database. The database folders allow us to right click and “Create Command File…”.

How do you handle your database changes? I’m always interested in suggestions.