Ryan LaNeve

I'm in the same boat as you - I've tried all sorts of solutions to putting my SQL into SCC - it's too much of a PITA because SQL doesn't integrate at all. The VS Editor is a nuisance for the job too.

It is possible to configure MS SQL Server to update VSS. If you install Visual Studio on a machine with SQL Server you'll see a whole bunch of stored procedures created. I've not actually set this up myself (it's on my very long TODO list) but I assume you've investigated this. Here is an MSDN article regarding configuring SQL Server 7 with VSS. I imagine SQL Server 2K would be similar.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html/sql7storprocvers.asp

The VSS intergration is really not a nice solution, at least not IMHO.

What I've found to work good is creating all object with QA, and saving the .sql files in sourcesafe. When we need to change a table, we check the file out VSS, load it in query analyzer and make the adjustments.

I agree with the people who say that VSS integration with sqlserver is not that good.

I'd go for a scheduled process that uses SQLDMO to generate a script with all the stored procs. The process uses COM to communicate with VSS and first checks out the script file, then generates using DMO the script and then checks the script back in. Because it is COM, it can be done in not that much lines of code using .NET or even VB.

Then you have it automated so no-one can forget to check in/out the stored procedure code.

We also use .sql files in sourcesafe with a little VB proggie to do the extract from the database. Stored procedures and triggers are versioned as is, whereas tables are versioned as a script which takes the previous version and makes changes without losing any existing data...but yes, it is a PITA.

This process is hard to get right. Rote scripting doesn't necessarily work because some changes are subtle and hard to infer through automation and you ALWAYS need to figure out a way to preserve data on the target.

Objects outside of tables(SPs,views,UDF,etc) are easy because you don't have to preserve data.

Tables are the hardest.

We try to do the following:
1) Table changes can only be made in major releases!
2) In a major release, don't change any tables!
3) If you must change a table, do it so it can be done with an ALTER TABLE(not a copy). Education is key here. Developers need to understand that changing a table is a huge impact and must be done with thought and care. Some tables have several 100 million rows....
4) Developers provide a change script for all schema updates. We automatically hook VSS checkin and associate all changes(code and DB) to an incident for packaging.
5) Our packaging system assembles all scripts in the correct dependency order and produces a full upgrade script. This is automatically tested on the previous version with each build. Errors are sent to developers.


There is still no integrated version control solution that handles all objects(from MSFT).

I really like MssqlExpress from xpressaps. We will be rolling this out in our next release. It handles all objects and even versioning change scripts. I think it is farily priced.

http://www.xpressapps.com/

MssqlExpress sounds interesting, but I should have mentioned we're not using VSS.

It certainly seems we're not alone in our frustration. We're already underway in setting up an automated system of check-out/script-with-SQLDMO/check-in. It's definitely a breeze to program. The only real issue then will be with table changes. Of course, if we have to manually generate scripts for one type of object instead of every type of object, perhaps we'll be a little more consistent about the process.

Thanks for everyone's comments.

I think they are adding support for a few other SC systems. I remember hearing StarTeam and SourceVault. Send them an email. They are very helpful.

sprugh-at-telution-dot-com

Apex SQL Edit supports VSS and soon Vault. $99