Very Computer

by **Frank LaRos** » Tue, 11 Apr 2000 04:00:00

My colleagues and I have the following problem:

(1) We develop a database and associated stored procedures on our
private server;
(2) At some point, we copy our work onto a production server;
(3) We go back to our private server and make changes, both to the
stored procedures and to the structure of the tables themselves;
(4) At some later point, we wish to roll all the changes from the
private system into the production system. Of course, we don't want the
production system to have to drop their tables and lose their data in
this process. We want to merge the changes in with the existing data.

Of course we could manually record each SQL statement we need to make
the changes occur later, but that is tedious and error-prone. Is there a
tool or method available to automate this?

by **Patrick Loga** » Tue, 11 Apr 2000 04:00:00

You don't necessarily have to drop tables to modify them. Might checkout
the functionality now available with the ALTER syntax. As far as
automating, I'll leave that to the gurus in the newsgroup.

My $0.02
--
Patrick Logan, MCSD
Senior Technical Development Advisor
McKessonHBOC -- Extended Care Solutions Group

Opinions expressed are my own and not necessarily those of McKessonHBOC.

by **Frank LaRos** » Tue, 11 Apr 2000 04:00:00

Makes sense. I guess I was trying to avoid having to work out the ALTER
statements manually.

What I'm looking for is a tool that can, in effect, look at two versions
of a database and automatically generate the ALTER statements to turn
one into the other. Another way to look at it is having a tool that
could log each ALTER statement I execute on the database (even if I
execute them through the use of a visual tool, such as the database
diagram) and allow me to repeat those statements against another version
of the database.

> You don't necessarily have to drop tables to modify them. Might checkout
> the functionality now available with the ALTER syntax. As far as
> automating, I'll leave that to the gurus in the newsgroup.

> My $0.02
> --
> Patrick Logan, MCSD
> Senior Technical Development Advisor
> McKessonHBOC -- Extended Care Solutions Group

> Opinions expressed are my own and not necessarily those of McKessonHBOC.

by **Tibor Karasz** » Wed, 12 Apr 2000 04:00:00

Frank,

I've never really _used_ this functionality, but the Design Table window has
a "change script" functionality. "Edit" a table (structure) and press the
"Save Change script" button. You also have the option to generate such
scripts automatically.

Note that EM doesn't perform these structural changes in the most efficient
way. You can see that EM generally creates a temp table and then copy all
data from the original to the temp (and then rename and drop). Even though a
simple (and more efficient) ALTER TABLE would have been sufficient...

--
Tibor Karaszi, Cornerstone Sweden AB
MCDBA, MCSE, MCSD, MCT, SQL Server MVP
Please reply to the newsgroup only, not by email.

> Makes sense. I guess I was trying to avoid having to work out the ALTER
> statements manually.

> What I'm looking for is a tool that can, in effect, look at two versions
> of a database and automatically generate the ALTER statements to turn
> one into the other. Another way to look at it is having a tool that
> could log each ALTER statement I execute on the database (even if I
> execute them through the use of a visual tool, such as the database
> diagram) and allow me to repeat those statements against another version
> of the database.

> > You don't necessarily have to drop tables to modify them. Might
checkout
> > the functionality now available with the ALTER syntax. As far as
> > automating, I'll leave that to the gurus in the newsgroup.

> > My $0.02
> > --
> > Patrick Logan, MCSD
> > Senior Technical Development Advisor
> > McKessonHBOC -- Extended Care Solutions Group

> > Opinions expressed are my own and not necessarily those of McKessonHBOC.

by **Peter A. Schot** » Wed, 12 Apr 2000 04:00:00

Frank,
You may want to consider a 3rd party tool. I know that ER/Studio and
Schema Manager from Embarcadero can do something like this
(www.embarcadero.com). As far as ERD software goes, they're relatively
inexpensive and the programs are pretty good. The latest version of
ER/Studio, 4.0, allows scripting of triggers and procedures as well. When you
update the database, it will allow you to choose which objects to merge. I
believe that altering the database generates a script by default. It may not
be exactly what you're looking for, but it might be worth checking out.

Other ERD software should be able to do this as well. I'm pretty sure
that ERWin can handle this well. Others on this group should have some
advice, I'm sure.

HTH,
-Pete Schott

> Makes sense. I guess I was trying to avoid having to work out the ALTER
> statements manually.

> What I'm looking for is a tool that can, in effect, look at two versions
> of a database and automatically generate the ALTER statements to turn
> one into the other. Another way to look at it is having a tool that
> could log each ALTER statement I execute on the database (even if I
> execute them through the use of a visual tool, such as the database
> diagram) and allow me to repeat those statements against another version
> of the database.

> > You don't necessarily have to drop tables to modify them. Might checkout
> > the functionality now available with the ALTER syntax. As far as
> > automating, I'll leave that to the gurus in the newsgroup.

> > My $0.02
> > --
> > Patrick Logan, MCSD
> > Senior Technical Development Advisor
> > McKessonHBOC -- Extended Care Solutions Group

> > Opinions expressed are my own and not necessarily those of McKessonHBOC.

by **gregkeller9..** » Fri, 14 Apr 2000 04:00:00

Indeed, Peter is correct.

We manufacture two products in our catalogue that will enable DBAs to
tackle the concept of Change Management. One is ER/Studio, a data
modeling application and the other, DBArtisan Change Manager. Both
these products utilize the same 'engine' to manufacture the intelligent
ALTER scripts (DBMS and DBMS version-specific) and ultimately handle
the concept of making one database 'look' like another. ER/Studio
utilizes a more traditional approach of utilizing data models as the
foundation of comparison. We offer DBArtisan Change Manager as a very
robust companion to our successful DBArtisan environment as a highly
GUI-driven method of comparing databases without having to have any
modeling-savvy whatsoever.

You've got your choice basically! I hope we can help you in some way to
help make this process simple and manageable for you. Visit
http://www.embarcadero.com to download any of these products to try.

Regards,

Greg Keller
Product Manager-ER/Studio
Embarcadero Technologies, Inc

> Frank,
> You may want to consider a 3rd party tool. I know that
ER/Studio and
> Schema Manager from Embarcadero can do something like this
> (www.embarcadero.com). As far as ERD software goes, they're
relatively
> inexpensive and the programs are pretty good. The latest version of
> ER/Studio, 4.0, allows scripting of triggers and procedures as well.
When you
> update the database, it will allow you to choose which objects to
merge. I
> believe that altering the database generates a script by default. It
may not
> be exactly what you're looking for, but it might be worth checking
out.

> Other ERD software should be able to do this as well. I'm
pretty sure
> that ERWin can handle this well. Others on this group should have
some
> advice, I'm sure.

> HTH,
> -Pete Schott

> > Makes sense. I guess I was trying to avoid having to work out the
ALTER
> > statements manually.

> > What I'm looking for is a tool that can, in effect, look at two
versions
> > of a database and automatically generate the ALTER statements to
turn
> > one into the other. Another way to look at it is having a tool that
> > could log each ALTER statement I execute on the database (even if I
> > execute them through the use of a visual tool, such as the database
> > diagram) and allow me to repeat those statements against another
version
> > of the database.

> > > You don't necessarily have to drop tables to modify them. Might
checkout
> > > the functionality now available with the ALTER syntax. As far as
> > > automating, I'll leave that to the gurus in the newsgroup.

> > > My $0.02
> > > --
> > > Patrick Logan, MCSD
> > > Senior Technical Development Advisor
> > > McKessonHBOC -- Extended Care Solutions Group

> > > Opinions expressed are my own and not necessarily those of
McKessonHBOC.

Sent via Deja.com http://www.deja.com/
Before you buy.

Very Computer

database versioning problem

database versioning problem

database versioning problem

database versioning problem

database versioning problem

database versioning problem

database versioning problem