'Generate scripts' functionality in SQL 2005 not being consistent   

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?

date: Fri, 3 Jul 2009 09:07:01 -0700   author:   unknown

Re: 'Generate scripts' functionality in SQL 2005 not being consistent   

devprog@msbcomp.com (devprogmsbcompcom@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, esquel@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

date: Fri, 03 Jul 2009 15:42:22 -0700   author:   Erland Sommarskog

Re: 'Generate scripts' functionality in SQL 2005 not being consistent   

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


"devprog@msbcomp.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?

date: Sat, 4 Jul 2009 09:18:20 -0500   author:   TheSQLGuru

Re: 'Generate scripts' functionality in SQL 2005 not being consistent   

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

"devprog@msbcomp.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?

date: Sun, 5 Jul 2009 11:00:16 -0700   author:   Jeffrey Williams

RE: 'Generate scripts' functionality in SQL 2005 not being consistent   

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

"devprog@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?

date: Sun, 5 Jul 2009 21:11:01 -0700   author:   Linchi Shea