See Comments below

Louis van Alphen wrote:

> We have exactly the same situation.
>
> There are tools out there that can determine the metadata difference and
> generate a script to bring the target DB in line with the source DB. But
> these tools are far from perfect. They dont look at dependencies and just
> generate a script. You have to put the parts in the correct order
> yourself.

I am familiar with one of these. It helps but is a long ways away from
allowing users to install releases themselves.

>
> E.g. if a SP has changed by adding or removing a parameter, you have to
> disable the calls to this sp in all dependent sps before you add the
> param
> otherwise you break the db. Only then you re-enable the calls to the
> changed sp.

One way of getting around this I have found is to alter all procedure to
nothing (except the input and output parameters. This way no procedures
are dependent on each other.

> This example is a 3-step process that none of the tools provide
> for. This becomes cumbersome for a DB with a lot of metadata.

The problems I have found is in dropping views as they can be dependent
on each other. I wish I understood the system tables regarding
dependencies better as maybe you could drop them in a certain order.
Another way is to just continually drop all of them several times until
there are none left.

Also when removing indexes, foreign keys and primary keys I often get
Index in Use errors and so I have disconnect and reconnect before it
will allow me to drop them.

>
>
> We are in the process of building a tool that will do this, but it is
> quite
> complex as you can quite imagine.

I hope you are building this tool in a way that you could market it. It
sounds like there is a great need for this.

>
>
> At the moment we keep close track of what we change and generate scripts
> for each part.

Us to, but what a pain in the butt. Right now we have 4 clients using
the system. All at different releases of the software. Once we get all
60 clients converted it is going to be a nightmare. We will probably
have to go to semi-annual releases or something which we really don't
want to do.

Thanks for the information.

Todd