February 20, 2003 at 1:46 pm
I'm trying to figure out a process so that our company can automate db schema's in the field via an install process. Our product uses SQL server as the bcak-end tier.
Originally, I had thought to have a master update file that would sequentially check a version number stored in the database and then apply updates as needed to get from whatever version was already installed to the current schema. Unfortunately, since a large number of the updates include stored procedure changes, T-SQL doesn't allow for batch statements in an 'if' clause.
e.g.-
if version < than x
begin
begin transaction
-- alter some table
go
-- drop and create some procedure
go
-- update the version number to x
commit transaction
end
Any thoughts on how to accomplish this (outside of a sql script for each version and a C++ program/sql script to apply them sequentially)? Hopefully, the solution also lends itself to being managed via a source control solution (like VSS) since there are multiple developers modifying the schema in the course of developing thier components.
Thanks for any thoughts.
Steve
February 20, 2003 at 2:17 pm
Im trying to solve a similar issue. What Im looking at now is about what you've described (limited to procs/views), run a job that scripts out all the objects, adds them to a version table. For each object I track which versionid should be deployed, plus a status - live, deprecated, delete. At times I just want to drop old procs from the target dbs. Just thinking I'll run some code to drop all the procs/views listed, then put the current version if they are live.
I considered VSS, took the easy way out just throwing it all into a table.
Andy
February 24, 2003 at 6:04 am
I have such a solution.
It works with VSS and OSQL Scripts.
But it is not as good as it should be. Because
the whole system starts or stops with 'Labeling in VSS'
and since these cannot be undone one error will make
the system faulty.
I Agree with Andy that building a nice Database and store
everything in this database is a better solution.
Only don't forget to script the database for every release and
check the script in into VSS.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply