Fog Creek Software
Discussion Board




Nightly Builds and Database Configuration

We are developing a MS SQL Server 2000 app.  We're using Visual Studio .NET and all of the great integrated database management features, such as being able to store create scripts for stored procedures, tables, etc. right in the project.

A rather nifty feature that's included in Visual Studio is the ability to build command files that, in turn, execute each of the scripts to create the tables and stored procedures.

All of the individual scripts are always up to date in the project, and I can retrieve them using Visual Build's SourceSafe integration. However, the problem is, to keep the command file up to date would require manual processing to trigger the creation of the most up-to-date file.

My question is the following: has someone out there figured out how to manipulate Visual Source Safe .NET using Visual Build, or another automated build tool, to generate this command file during the nightly build?  Or, do any of you have a better solution?  Or,  am I simply suffering from wishful thinking and there is no real solution except keeping a manually-created command file up to date.

Dave
Monday, January 27, 2003

What format is the file in? Is it text?

If it's binary, I have no idea. But if it's text format, you could use one of the standard unix text processing tools (Which have all been ported to windows, like Cygwin or activestate perl), like perl, sed, awk to parse it.

I suppose it depends on what kind of updates you need done, and I've not used those products, so I'm not sure. But I'm figuring if it's just text, you should be able to either modify the existing one, or generate a new one from a template or something.

Mike Swieton
Monday, January 27, 2003

I'm not sure if nAnt can do this, but you might want to have a look at http://nant.sourceforge.net

GiorgioG
Monday, January 27, 2003

Dave,

What tool are you using to generate you create scripts? And does that tool handle updates to the schema as well?

I'm just starting to look into this myself. So many options that I like to see what other have been using.

Marc
Monday, January 27, 2003

We're using the "Database Project" type in Visual Studio .NET to manage the scripts both for the database structure and stored procedures.  The scripts for the table structure are generated from the tables directly.  The stored procedure scripts are modified in Visual Studio, then the scripts are executed to re-create the stored procedure in the database.

The end result of this is that all of the scripts are able to be under source control with the integrated support for Source Safe, which is much better than depending upon a "master" database somewhere that's very difficult to version control.

There is also a "Create Command File" feature in Visual Studio which creates a CMD file that executes all of the scripts, allowing you to create the entire database.

What I'm trying to figure out is how I can automate the creation of the CMD file, so we don't have to do it manually every time a script is added.  I think I may have the build server run a Visual Studio macro to create the CMD file each night, but I'm still experimenting.

Thank you for all of the feedback, guys!

Dave
Tuesday, January 28, 2003

*  Recent Topics

*  Fog Creek Home