'Generate scripts' functionality in SQL 2005 not being consistent

3 Jul 2009 4:07 PM

devprog@msbcomp.com

I'm running SQL 2005 (version 9.0.4035) and I like to use the 'Generate
scripts' functionality in SQL Server Management Studio to create one big file
with the scripts for an entire db as a way to track changes to the various
objects in the db.

Problem is that in 2005 the 'Generate scripts' feature does not always seem
to generate objects in the same order when writing out the script file, even
though I make sure to use always the same settings as before. Trying to
compare two text files with stuff all over the place makes it next to
impossible to detect true changes to objects.

I can't figure out what makes this particular functionality behave like this
- it was rock solid in SQL 2000 where objects would always appear in the same
order in the file so true changes could easily be spotted.

In 2005 there is a lot of 'noise' when trying to compare files and look for
changes given that objects in the file are moved around.

Did anybody else notice this? Anybody have an idea on how to possibly fix
this? Anybody have another way to easily track changes to db objects over
time?

3 Jul 2009 10:42 PM

Erland Sommarskog

devp***@msbcomp.com (devprogmsbcomp***@discussions.microsoft.com) writes:

> I'm running SQL 2005 (version 9.0.4035) and I like to use the 'Generate
> scripts' functionality in SQL Server Management Studio to create one big
> file with the scripts for an entire db as a way to track changes to the
> various objects in the db.
>
> Problem is that in 2005 the 'Generate scripts' feature does not always
> seem to generate objects in the same order when writing out the script
> file, even though I make sure to use always the same settings as before.
> Trying to compare two text files with stuff all over the place makes it
> next to impossible to detect true changes to objects.

Not really sure what you want the scripts for, but if you are into
comparing, have a look at SQL Compare from Red Gate. They do a far
better scripting job than what Microsoft does.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

4 Jul 2009 2:18 PM

TheSQLGuru

Another product is Script from ApexSQL. Very nice flexible tool for
generating what you seek. Also note that Apex has built-in source code
functionality that can be used to automate such comparisons of what has
changed in a database, as well as a Diff product to compare a live database
against either a live database, a snapshot or script file and I also believe
your source code copy.

Disclaimer: I have a close relationship with Apex, use their products and
recommend them to my clients. Also, if you care to you can mention
TheSQLGuru sent you you will get a discount and my daughter will get a few
coins for her college fund.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

Show quoteHide quote

"devp***@msbcomp.com" <devprogmsbcomp***@discussions.microsoft.com> wrote in
message news:F8486D48-96C7-4472-9AA5-7B88AA9D4888@microsoft.com...
>
> I'm running SQL 2005 (version 9.0.4035) and I like to use the 'Generate
> scripts' functionality in SQL Server Management Studio to create one big
> file
> with the scripts for an entire db as a way to track changes to the various
> objects in the db.
>
> Problem is that in 2005 the 'Generate scripts' feature does not always
> seem
> to generate objects in the same order when writing out the script file,
> even
> though I make sure to use always the same settings as before. Trying to
> compare two text files with stuff all over the place makes it next to
> impossible to detect true changes to objects.
>
> I can't figure out what makes this particular functionality behave like
> this
> - it was rock solid in SQL 2000 where objects would always appear in the
> same
> order in the file so true changes could easily be spotted.
>
> In 2005 there is a lot of 'noise' when trying to compare files and look
> for
> changes given that objects in the file are moved around.
>
> Did anybody else notice this? Anybody have an idea on how to possibly fix
> this? Anybody have another way to easily track changes to db objects over
> time?

5 Jul 2009 6:00 PM

Jeffrey Williams

I would recommend using any of the available database comparison tools that
are available. I personally use Redgate's SQL Compare for stuff like this.
With this tool, I have the ability to take a snapshot of the database and
then compare the database to that snapshot and view the changes.

If you do not want to purchase a tool (well worth it), there are free tools
available. Check out http://www.codeplex.com which has a few.

And finally, if you want to continue doing this basically the same way -
instead of a single file script everything out to individual files in a
designated directory. Then, use something like Beyond Compare
(http://www.scootersoftware.com) to perform a folder comparison.

Jeff

Show quoteHide quote

"devp***@msbcomp.com" <devprogmsbcomp***@discussions.microsoft.com> wrote in
message news:F8486D48-96C7-4472-9AA5-7B88AA9D4888@microsoft.com...
> I'm running SQL 2005 (version 9.0.4035) and I like to use the 'Generate
> scripts' functionality in SQL Server Management Studio to create one big
> file
> with the scripts for an entire db as a way to track changes to the various
> objects in the db.
>
> Problem is that in 2005 the 'Generate scripts' feature does not always
> seem
> to generate objects in the same order when writing out the script file,
> even
> though I make sure to use always the same settings as before. Trying to
> compare two text files with stuff all over the place makes it next to
> impossible to detect true changes to objects.
>
> I can't figure out what makes this particular functionality behave like
> this
> - it was rock solid in SQL 2000 where objects would always appear in the
> same
> order in the file so true changes could easily be spotted.
>
> In 2005 there is a lot of 'noise' when trying to compare files and look
> for
> changes given that objects in the file are moved around.
>
> Did anybody else notice this? Anybody have an idea on how to possibly fix
> this? Anybody have another way to easily track changes to db objects over
> time?

6 Jul 2009 4:11 AM

Linchi Shea

We generate scripts through SMO one file per object, and the object script
files are grouped under separate folders, one for each object category (e.g.
procs, triggers, tables, views, functions, constraints, roles, etc).

That makes it more manageable, not just for comparison, but for many other
source control purposes.

Linchi

Show quoteHide quote

"devp***@msbcomp.com" wrote:

> I'm running SQL 2005 (version 9.0.4035) and I like to use the 'Generate
> scripts' functionality in SQL Server Management Studio to create one big file
> with the scripts for an entire db as a way to track changes to the various
> objects in the db.
>
> Problem is that in 2005 the 'Generate scripts' feature does not always seem
> to generate objects in the same order when writing out the script file, even
> though I make sure to use always the same settings as before. Trying to
> compare two text files with stuff all over the place makes it next to
> impossible to detect true changes to objects.
>
> I can't figure out what makes this particular functionality behave like this
> - it was rock solid in SQL 2000 where objects would always appear in the same
> order in the file so true changes could easily be spotted.
>
> In 2005 there is a lot of 'noise' when trying to compare files and look for
> changes given that objects in the file are moved around.
>
> Did anybody else notice this? Anybody have an idea on how to possibly fix
> this? Anybody have another way to easily track changes to db objects over
> time?