Automatically Generating SQL change Script

[Lau]

Hi all,

I can't find the option in Enterprise Manager that turns on automatic scripting
of all changes made to the database. So that I can save the SQL script to
a file. I know its there somewhere but can't find it.

I'm not talking about All Tasks > Generate Script... that only does it when
I specifically go to that option. I want the one that generates a script
file automatically every time I save changes to a diagram or in design table
etc.

Completely frustrated.
Help!

Lau

[David Satz]

AFAIK there is no such option, but would love to have it.
--
HTH,
David Satz
Principal Web Engineer
Hyperion Solutions
{ SQL Server 2000 SP2/6.5 SP5a } { Cold Fusion 5 SP1 } { VSS }
(Please reply to group only - emails answered rarely)
-----------------------------------------------------------------
"Lau" <lau@goldenweb.com.au> wrote in message news:3d3d0d37$1@10.1.10.29...
>
> Hi all,
>
> I can't find the option in Enterprise Manager that turns on automatic
scripting
> of all changes made to the database. So that I can save the SQL script to
> a file. I know its there somewhere but can't find it.
>
> I'm not talking about All Tasks > Generate Script... that only does it
when
> I specifically go to that option. I want the one that generates a script
> file automatically every time I save changes to a diagram or in design
table
> etc.
>
> Completely frustrated.
> Help!
>
> Lau

[Lau]

It's definitely there somewhere... I have been using it for several months
but then I upgraded my computer and had to reinstall all my software nad
now can't find where to turn the bloody thing on again.

Peace
Lau

"David Satz" <davidNOSPAMsatz@yahoo.NOSPAM.com> wrote:
>AFAIK there is no such option, but would love to have it.
>--
>HTH,
>David Satz
>Principal Web Engineer
>Hyperion Solutions
>{ SQL Server 2000 SP2/6.5 SP5a } { Cold Fusion 5 SP1 } { VSS }
>(Please reply to group only - emails answered rarely)
>-----------------------------------------------------------------
>"Lau" <lau@goldenweb.com.au> wrote in message news:3d3d0d37$1@10.1.10.29...
>>
>> Hi all,
>>
>> I can't find the option in Enterprise Manager that turns on automatic
>scripting
>> of all changes made to the database. So that I can save the SQL script
to
>> a file. I know its there somewhere but can't find it.
>>
>> I'm not talking about All Tasks > Generate Script... that only does it
>when
>> I specifically go to that option. I want the one that generates a script
>> file automatically every time I save changes to a diagram or in design
>table
>> etc.
>>
>> Completely frustrated.
>> Help!
>>
>> Lau
>
>

[Lau]

Yeeehaaa!!
Got the answer on another list!

SQL Server 2000:
In the table design view in EM, it's the third button.
Make a change to the table and the button becomes active.
Click it and it pops up the change script. At the bottom of this popup is
a checkbox that says; "Automatically generate change script on every save."
Tick that little box and this popup will be generated for all future changes.

Only bummer is it doesn't automatically generate change scripts for stored
procedures.

Peace
Lau

[David Satz]

I just save all my scripts and put them into our version control software:
MS VSS

Dave
"Lau" <lau@goldenweb.com.au> wrote in message news:3d3e2490$1@10.1.10.29...
>
> Yeeehaaa!!
> Got the answer on another list!
>
> SQL Server 2000:
> In the table design view in EM, it's the third button.
> Make a change to the table and the button becomes active.
> Click it and it pops up the change script. At the bottom of this popup is
> a checkbox that says; "Automatically generate change script on every
save."
> Tick that little box and this popup will be generated for all future
changes.
>
> Only bummer is it doesn't automatically generate change scripts for stored
> procedures.
>
> Peace
> Lau
>
>
>
>

[Michael Cole]

David Satz wrote:
> I just save all my scripts and put them into our version control
> software: MS VSS

Yes, but this option is useful if you have existing databases out there
where you need to change a table structure but not lose existing data.


> Dave
> "Lau" <lau@goldenweb.com.au> wrote in message
> news:3d3e2490$1@10.1.10.29...
>>
>> Yeeehaaa!!
>> Got the answer on another list!
>>
>> SQL Server 2000:
>> In the table design view in EM, it's the third button.
>> Make a change to the table and the button becomes active.
>> Click it and it pops up the change script. At the bottom of this
>> popup is a checkbox that says; "Automatically generate change script
>> on every
> save."
>> Tick that little box and this popup will be generated for all future
> changes.
>>
>> Only bummer is it doesn't automatically generate change scripts for
>> stored procedures.

>>
>> Peace
>> Lau

[Michael Cole]

Lau wrote:
> Yeeehaaa!!
> Got the answer on another list!
>
> SQL Server 2000:
> In the table design view in EM, it's the third button.
> Make a change to the table and the button becomes active.
> Click it and it pops up the change script. At the bottom of this
> popup is a checkbox that says; "Automatically generate change script
> on every save." Tick that little box and this popup will be generated
> for all future changes.
>
> Only bummer is it doesn't automatically generate change scripts for
> stored procedures.

Why would you need a change script for a stored procedure? It can be simply
blown away and recreated without loss of data. Change scripts are only
needed to ensure that there is no loss of data. In fact, if you read the
script, the only difference between it and a creation script is that it uses
a temp table, and copies existing data across before blowing away the
original table design. Stored procedures have no data.


--
Regards,

Michael Cole

[Bonnie Kaiser]

"David Satz" <davidNOSPAMsatz@yahoo.NOSPAM.com> wrote:
>AFAIK there is no such option, but would love to have it.
>--
>HTH,
>David Satz
>Principal Web Engineer
>Hyperion Solutions
>{ SQL Server 2000 SP2/6.5 SP5a } { Cold Fusion 5 SP1 } { VSS }
>(Please reply to group only - emails answered rarely)
>-----------------------------------------------------------------
>"Lau" <lau@goldenweb.com.au> wrote in message news:3d3d0d37$1@10.1.10.29...
>>
>> Hi all,
>>
>> I can't find the option in Enterprise Manager that turns on automatic
>scripting
>> of all changes made to the database. So that I can save the SQL script
to
>> a file. I know its there somewhere but can't find it.
>>
>> I'm not talking about All Tasks > Generate Script... that only does it
>when
>> I specifically go to that option. I want the one that generates a script
>> file automatically every time I save changes to a diagram or in design
>table
>> etc.
>>
>> Completely frustrated.
>> Help!
>>
>> Lau
>
>
Lau,

I agree with David, I would LOVE to have such a utility, but there isn't
one as part of SQL Server.

There are Database Design tools like Erwin or Power Designer that claim to
do just that. These tools provide methods for representing graphically
both a logical and physical database model. You can then select the particular
DB Engine and generate the scripts for creating that database. The tools
claim that they provide support for tables, attributes, relations, indexes,
and even triggers. Both tools claim that you can archive a particular physical
DB Schema, make your modifications, then generate a 'change script' to make
those same modifications to your actual database.
My experience is that the scripts created by these tools (initial creation
scripts or change scripts) will actually only provide a good place to start
(typically 80%). I have always had to modify the scripts a bit further
to effect the changes that I want without any errors or side effects. Testing
and verification is required.

Bonnie Jo Kaiser
Software Engineer
Medicomp, Inc.