Managing DB updates to client DB servers

I'm looking for ideas on how to write SQL scripts for updates that are
pushed out to clients for product updates. Obviously, We could just
keep track of the changes on a pad or write a database that requires
us to input those changes and eventually hand write the update
scripts. I was wondering if anybody has any solutions that may help
automate this process.

Is there a way to write an application that will compare a current
(updated) database structure against the last realease that will give
us the fields that need to be changed?

As far as creating the scripts for the initial install, thats easy. We
can do that right from the SQL Enterprise Manager.

Call me lazy! Any ideas?

ThanksDuncan (duncan.loxton@.gmail.com) writes:
> I'm looking for ideas on how to write SQL scripts for updates that are
> pushed out to clients for product updates. Obviously, We could just
> keep track of the changes on a pad or write a database that requires
> us to input those changes and eventually hand write the update
> scripts. I was wondering if anybody has any solutions that may help
> automate this process.
> Is there a way to write an application that will compare a current
> (updated) database structure against the last realease that will give
> us the fields that need to be changed?
> As far as creating the scripts for the initial install, thats easy. We
> can do that right from the SQL Enterprise Manager.

There a couple of products on the market. SQLCompare from Red Gate does
indeed compare two databases. DBGhost likes to tout itself as being
good for this. I have not use any of them.

Whatever method, you should keep all your code under version control,
and all your update scripts should have their foundation in the
version-control system. Basically a shipment is all changes between
the label for the previous shipment and this one. With some files added,
like triggers or indexes for changed tables.

In fact, once you have a good version control system up and running,
composing your scripts manually is not daunting task - but admittedly
it becomes boring after a while. The flip side is that you learn to
understand your process.

In fact, I started in our shop with something like this many years
ago. This has now evolved to a versatile toolset that we use. It is
available as freeware for anyone who want to try, see
http://www.abaris.se/abaperls/index.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Management Studio: Project Hierarchy

I created a project type "SQL Server Scripts" in "Microsoft SQL Server
Management Studio", and the default structure created was:
+ Project Name
+ Connections (Type 2)
+ Queries (Type 0)
+ Miscellaneous (Type 3)
How can I create new folders? I'd like to organize my queries by subject
(Finance, Human Resources, Marketing, etc)
I tried to edit the project file (*.ssmssqlproj) to add new Logical Folders,
but it seems those folders are based on "Type" and you cannot have multiple
folders of the same type.
<LogicalFolder Name="Queries" Type="0">
After frustrating attempts, I decided to use the existing hierarchy. I added
my existing stored procedures to the "Queries" folder and realized the files
were not in alphabetical order. It's impossible to find anything when list
contains more than a thousand queries.
Am I using the SQL MANAGEMENT STUDIO correctly? If so, what's the purpose of
creating a SQL SCRIPT PROJECT if you cannot organize the files the way you
want? Please help!!!
I posted the same question a few days ago, and I also played around with the
project file to no avial. Someone replied then that this is an outstanding
issue with Microsoft. Please go to the site, vote on the issue, and add your
voice. If we don't complain, MS isn't going to do anything about it:
http://lab.msdn.microsoft.com/produc...7-be25db5080df
My feeling is that the whole VS 2005 suite, including Management Studio and
Team System, was rushed out and shipped half cooked.
Having replaced the graceful Query Analyzer and Enterprise Manager with a
lumberous piece of bloatware (VS 2005 engine), you'd expect them to
compensate the developers/DBA's by allowing them to integrate SSMS with VS
2005 solutions or at least give them the ability to organize their projects
in a meaningful way.
"YYZRic" wrote:

> I created a project type "SQL Server Scripts" in "Microsoft SQL Server
> Management Studio", and the default structure created was:
> + Project Name
> + Connections (Type 2)
> + Queries (Type 0)
> + Miscellaneous (Type 3)
> How can I create new folders? I'd like to organize my queries by subject
> (Finance, Human Resources, Marketing, etc)
> I tried to edit the project file (*.ssmssqlproj) to add new Logical Folders,
> but it seems those folders are based on "Type" and you cannot have multiple
> folders of the same type.
> <LogicalFolder Name="Queries" Type="0">
> After frustrating attempts, I decided to use the existing hierarchy. I added
> my existing stored procedures to the "Queries" folder and realized the files
> were not in alphabetical order. It's impossible to find anything when list
> contains more than a thousand queries.
> Am I using the SQL MANAGEMENT STUDIO correctly? If so, what's the purpose of
> creating a SQL SCRIPT PROJECT if you cannot organize the files the way you
> want? Please help!!!
>
>
>
|||Thanks Buddie, I cast my vote!
"Homam" wrote:
[vbcol=seagreen]
> I posted the same question a few days ago, and I also played around with the
> project file to no avial. Someone replied then that this is an outstanding
> issue with Microsoft. Please go to the site, vote on the issue, and add your
> voice. If we don't complain, MS isn't going to do anything about it:
> http://lab.msdn.microsoft.com/produc...7-be25db5080df
> My feeling is that the whole VS 2005 suite, including Management Studio and
> Team System, was rushed out and shipped half cooked.
> Having replaced the graceful Query Analyzer and Enterprise Manager with a
> lumberous piece of bloatware (VS 2005 engine), you'd expect them to
> compensate the developers/DBA's by allowing them to integrate SSMS with VS
> 2005 solutions or at least give them the ability to organize their projects
> in a meaningful way.
>
>
> "YYZRic" wrote:

Management Studio: Fatal Scripting Error

I'm trying to add more than one view to my database in a script. While the view in my scripts are different than the example below, it illustrates the problem. Essentially, when I try to add more than one view, I'm getting a parsing error on the word "GO". The script works OK in SQL 2000 Query Analyzer but fails in Management Studio. I've installed the SQL 2005 Service Pack 1 CTP on my Windows XP SP2 workstation. When I run only the top or bottom portion of the view, the respective view script parses and runs.
Here is the script:
CREATE view [dbo].[Object_View] as
select * from sysobjects where name = 'sysobjects'
GO
CREATE view [dbo].[Column_View] as
select * from table_detail where name = 'syscolumns'
GO
Here is the error:
A fatal scripting error occurred.
Incorrect syntax was encountered while parsing GO.
I found the problem. I was using an old script that had been set up for both Oracle and SQL Server to generate the views from metadata. The scripts were generating a CHAR(13) for carriage return without a CHAR(10) for line feed. This caused an error in Management Studio - but curiously not in Query Analyzer.
Is anybody else finding little idiosyncrasies in this that make Query Analyzer look still good? There are some things I really like in Management Studio but things like this drive me crazy. I really don't like that I can't script the DROP and PERMISSIONS when I script an object - at least I haven't found the configuration yet.
Chuck Hawkins
"Dr. Network" <charles.hawkins@.jenzabar.net> wrote in message news:%23CIX4S1VGHA.5100@.TK2MSFTNGP11.phx.gbl...
I'm trying to add more than one view to my database in a script. While the view in my scripts are different than the example below, it illustrates the problem. Essentially, when I try to add more than one view, I'm getting a parsing error on the word "GO". The script works OK in SQL 2000 Query Analyzer but fails in Management Studio. I've installed the SQL 2005 Service Pack 1 CTP on my Windows XP SP2 workstation. When I run only the top or bottom portion of the view, the respective view script parses and runs.
Here is the script:
CREATE view [dbo].[Object_View] as
select * from sysobjects where name = 'sysobjects'
GO
CREATE view [dbo].[Column_View] as
select * from table_detail where name = 'syscolumns'
GO
Here is the error:
A fatal scripting error occurred.
Incorrect syntax was encountered while parsing GO.
|||This was addressed in Bug ID FDBK44155.
Chuck Hawkins
"Dr. Network" <charles.hawkins@.jenzabar.net> wrote in message news:ucmK$k1VGHA.5592@.TK2MSFTNGP09.phx.gbl...
I found the problem. I was using an old script that had been set up for both Oracle and SQL Server to generate the views from metadata. The scripts were generating a CHAR(13) for carriage return without a CHAR(10) for line feed. This caused an error in Management Studio - but curiously not in Query Analyzer.
Is anybody else finding little idiosyncrasies in this that make Query Analyzer look still good? There are some things I really like in Management Studio but things like this drive me crazy. I really don't like that I can't script the DROP and PERMISSIONS when I script an object - at least I haven't found the configuration yet.
Chuck Hawkins
"Dr. Network" <charles.hawkins@.jenzabar.net> wrote in message news:%23CIX4S1VGHA.5100@.TK2MSFTNGP11.phx.gbl...
I'm trying to add more than one view to my database in a script. While the view in my scripts are different than the example below, it illustrates the problem. Essentially, when I try to add more than one view, I'm getting a parsing error on the word "GO". The script works OK in SQL 2000 Query Analyzer but fails in Management Studio. I've installed the SQL 2005 Service Pack 1 CTP on my Windows XP SP2 workstation. When I run only the top or bottom portion of the view, the respective view script parses and runs.
Here is the script:
CREATE view [dbo].[Object_View] as
select * from sysobjects where name = 'sysobjects'
GO
CREATE view [dbo].[Column_View] as
select * from table_detail where name = 'syscolumns'
GO
Here is the error:
A fatal scripting error occurred.
Incorrect syntax was encountered while parsing GO.

Management Studio, Save as... dialog before running External Tools

Hi.

In Management Studio I have defined some external tools. When I call my external tool from Tools menu and I have query scripts that are not saved I get "Save File As..." dialog for EVERY not saved script before running my External Tool. This is frustrating because sometimes I have a lot of not saved scripts and my External Tool doesn't need saved script files. How to run External tool in Mangement Studio without this frustrating "Save File As..." dialog box?

Kamil

I get the same thing. I can click cancel and the tool still opens, but it is annoying.

Management Studio scripts for SQL 2000

When I use the generate scripts task in Management Studio to create tables
for a SQL 2000 target, the scripts do not run in SQL 2000.
I selected SQL 2000 compatibility mode. The scripts work fine on a 2000 database
running under SQL 2005 but not on the real thing.
Is there a easy way to generate scripts that run on a SQL 2000 instance?
Darren (Darren@.nospam.nospam) writes:
> When I use the generate scripts task in Management Studio to create tables
> for a SQL 2000 target, the scripts do not run in SQL 2000.
Exactly why do they not run? Because they refer to sys.objects, of because
of some other problem? I just scripted a database on SQL 2005 for SQL 2000,
and the script ran on SQL 2000.
I seem to recall that that there was a problem with scripts for SQL 2000
referring to sys.objects in the RTM release ot SQL 2005, but this was
corrected in SP1. You can find SP1 of SQL 2005 at
http://www.microsoft.com/sql/sp1.mspx.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Thanks. I wasn't aware of SP1.
Erland Sommarskog wrote:
> Darren (Darren@.nospam.nospam) writes:
> Exactly why do they not run? Because they refer to sys.objects, of because
> of some other problem? I just scripted a database on SQL 2005 for SQL 2000,
> and the script ran on SQL 2000.
> I seem to recall that that there was a problem with scripts for SQL 2000
> referring to sys.objects in the RTM release ot SQL 2005, but this was
> corrected in SP1. You can find SP1 of SQL 2005 at
> http://www.microsoft.com/sql/sp1.mspx.
>
>