generating database scripts - VERY SLOW

Has anybody else experienced how long it takes to generate scripts from
Management Studio? I'm launching the wizard (right click database/ tasks /
generate scripts). The wizard takes me through a couple of screen to set my
options. I finally get to the window where it's processing the objects.
After 10 minutes, only 3 objects have been completed. A database with
hundreds of tables and views will take hours.
Is there any way to speed this up? Right clicking on a table or view and
generating the DDL for that one object only takes a few seconds.
It was never this slow in SQL Server 2000.The original release of SSMS was, yes, very slow. I think it is a bit
better in more recent builds, have you applied SP2 to your client tools?
"johnl" <johnl@.discussions.microsoft.com> wrote in message
news:7923D6FA-827E-4C50-85EE-64CCCEE21270@.microsoft.com...
> Has anybody else experienced how long it takes to generate scripts from
> Management Studio? I'm launching the wizard (right click database/ tasks
> /
> generate scripts). The wizard takes me through a couple of screen to set
> my
> options. I finally get to the window where it's processing the objects.
> After 10 minutes, only 3 objects have been completed. A database with
> hundreds of tables and views will take hours.
> Is there any way to speed this up? Right clicking on a table or view and
> generating the DDL for that one object only takes a few seconds.
> It was never this slow in SQL Server 2000.|||Not sure what version I'm at, don't know if this is SP2 or not?
Microsoft SQL Server Management Studio 9.00.3042.00|||That's SP2.
You can always find the service pack level by running
Select ServerProperty('ProductLevel')
Tom
"johnl" <johnl@.discussions.microsoft.com> wrote in message
news:C73B5E5D-5442-4619-BC54-75E697C9D733@.microsoft.com...
> Not sure what version I'm at, don't know if this is SP2 or not?
> Microsoft SQL Server Management Studio 9.00.3042.00
>|||> That's SP2.
> You can always find the service pack level by running
> Select ServerProperty('ProductLevel')
(But that doesn't tell you the version of client tools...)|||Several connect items I have reviewed seem to indicate that Microsoft thinks
they have solved this problem (or in a couple of cases, they mention some of
the issues brought up in the item, but not the speed). It is unclear after
reading them all whether they think they have fixed this in SP2 or the
typically vague "future release(s)"...
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=259838
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125313
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=228719
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126912
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=259838
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=262072
There are probably others; I did not perform an exhaustive search. Just
wanted to show you that you are not alone, and that some in Redmond seem to
think that it is fixed. :-)
"johnl" <johnl@.discussions.microsoft.com> wrote in message
news:C73B5E5D-5442-4619-BC54-75E697C9D733@.microsoft.com...
> Not sure what version I'm at, don't know if this is SP2 or not?
> Microsoft SQL Server Management Studio 9.00.3042.00
>|||True. Thanks for catching that.
"Aaron Bertrand [SQL Server]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eJK5jXBnIHA.1212@.TK2MSFTNGP05.phx.gbl...
>> That's SP2.
>> You can always find the service pack level by running
>> Select ServerProperty('ProductLevel')
> (But that doesn't tell you the version of client tools...)

generating database scripts - VERY SLOW

Has anybody else experienced how long it takes to generate scripts from
Management Studio? I'm launching the wizard (right click database/ tasks /
generate scripts). The wizard takes me through a couple of screen to set my
options. I finally get to the window where it's processing the objects.
After 10 minutes, only 3 objects have been completed. A database with
hundreds of tables and views will take hours.
Is there any way to speed this up? Right clicking on a table or view and
generating the DDL for that one object only takes a few seconds.
It was never this slow in SQL Server 2000.
The original release of SSMS was, yes, very slow. I think it is a bit
better in more recent builds, have you applied SP2 to your client tools?
"johnl" <johnl@.discussions.microsoft.com> wrote in message
news:7923D6FA-827E-4C50-85EE-64CCCEE21270@.microsoft.com...
> Has anybody else experienced how long it takes to generate scripts from
> Management Studio? I'm launching the wizard (right click database/ tasks
> /
> generate scripts). The wizard takes me through a couple of screen to set
> my
> options. I finally get to the window where it's processing the objects.
> After 10 minutes, only 3 objects have been completed. A database with
> hundreds of tables and views will take hours.
> Is there any way to speed this up? Right clicking on a table or view and
> generating the DDL for that one object only takes a few seconds.
> It was never this slow in SQL Server 2000.
|||Not sure what version I'm at, don't know if this is SP2 or not?
Microsoft SQL Server Management Studio 9.00.3042.00
|||That's SP2.
You can always find the service pack level by running
Select ServerProperty('ProductLevel')
Tom
"johnl" <johnl@.discussions.microsoft.com> wrote in message
news:C73B5E5D-5442-4619-BC54-75E697C9D733@.microsoft.com...
> Not sure what version I'm at, don't know if this is SP2 or not?
> Microsoft SQL Server Management Studio 9.00.3042.00
>
|||> That's SP2.
> You can always find the service pack level by running
> Select ServerProperty('ProductLevel')
(But that doesn't tell you the version of client tools...)
|||Several connect items I have reviewed seem to indicate that Microsoft thinks
they have solved this problem (or in a couple of cases, they mention some of
the issues brought up in the item, but not the speed). It is unclear after
reading them all whether they think they have fixed this in SP2 or the
typically vague "future release(s)"...
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=259838
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125313
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=228719
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126912
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=259838
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=262072
There are probably others; I did not perform an exhaustive search. Just
wanted to show you that you are not alone, and that some in Redmond seem to
think that it is fixed. :-)
"johnl" <johnl@.discussions.microsoft.com> wrote in message
news:C73B5E5D-5442-4619-BC54-75E697C9D733@.microsoft.com...
> Not sure what version I'm at, don't know if this is SP2 or not?
> Microsoft SQL Server Management Studio 9.00.3042.00
>
|||True. Thanks for catching that.
"Aaron Bertrand [SQL Server]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eJK5jXBnIHA.1212@.TK2MSFTNGP05.phx.gbl...
> (But that doesn't tell you the version of client tools...)

generated insert statement

using ms sql 2005 express,
created a simple two field table
tried to configure the data adapter in vb 2003 and the wizard returns
"Generated INSERT statement. There were errors configuring the data adapter"
No description of the error. The insert, update and delete procs are not
generated.
What am I doing wrong'
--
Ranbdy KrofickI don't think that wizard works with 2005 - it reads the
meta data tables which are different in SQL Server 2005 than
they were in previous versions of SQL Server. You'll need to
write your own code - and you'll be better off anyway.
-Sue
On Mon, 5 Mar 2007 15:33:03 -0800, Randy Krofick
<randyk@.nwinds.com> wrote:

>using ms sql 2005 express,
>created a simple two field table
>tried to configure the data adapter in vb 2003 and the wizard returns
>"Generated INSERT statement. There were errors configuring the data adapter
"
>No description of the error. The insert, update and delete procs are not
>generated.
>What am I doing wrong'

generated insert statement

using ms sql 2005 express,
created a simple two field table
tried to configure the data adapter in vb 2003 and the wizard returns
"Generated INSERT statement. There were errors configuring the data adapter"
No description of the error. The insert, update and delete procs are not
generated.
What am I doing wrong'
--
Ranbdy KrofickI don't think that wizard works with 2005 - it reads the
meta data tables which are different in SQL Server 2005 than
they were in previous versions of SQL Server. You'll need to
write your own code - and you'll be better off anyway.
-Sue
On Mon, 5 Mar 2007 15:33:03 -0800, Randy Krofick
<randyk@.nwinds.com> wrote:
>using ms sql 2005 express,
>created a simple two field table
>tried to configure the data adapter in vb 2003 and the wizard returns
>"Generated INSERT statement. There were errors configuring the data adapter"
>No description of the error. The insert, update and delete procs are not
>generated.
>What am I doing wrong'

generated insert statement

using ms sql 2005 express,
created a simple two field table
tried to configure the data adapter in vb 2003 and the wizard returns
"Generated INSERT statement. There were errors configuring the data adapter"
No description of the error. The insert, update and delete procs are not
generated.
What am I doing wrong?
Ranbdy Krofick
I don't think that wizard works with 2005 - it reads the
meta data tables which are different in SQL Server 2005 than
they were in previous versions of SQL Server. You'll need to
write your own code - and you'll be better off anyway.
-Sue
On Mon, 5 Mar 2007 15:33:03 -0800, Randy Krofick
<randyk@.nwinds.com> wrote:

>using ms sql 2005 express,
>created a simple two field table
>tried to configure the data adapter in vb 2003 and the wizard returns
>"Generated INSERT statement. There were errors configuring the data adapter"
>No description of the error. The insert, update and delete procs are not
>generated.
>What am I doing wrong?

Generate SQL script order by table name

I use SQL Server 2005 scripts wizard to generate scripts for database. Is there any ways it generate script in the order of table name?

I need to compare all data tables between two databases. It is a crazy job if I compare one table by one table.

If I can generate database script in the order of table name, which will make my job easy a lot.

Thanks for any advise.

I'd recommend getting visual studio team edition for database professionals. It can do schema and data comparisons with a nice user interface.

If that's not an option, I'm sure you could find a free file compare utility on the web, so you can stop comparing scripts manually.

|||

I believe thats best option to do for a comparison of the objects within the database.

But there is no such option within generate scripts wizard for the table order, you have to edit the content once it is generated.

|||AnthonyMartin: My guess is that the original poster _is_ using a file comparison utility. The problem is when the order of the tables are rearranged, it looks like huge chunks have changed when in fact the only thing that changed was the order the objects were scripted.

I'm having the same problem as the original poster. Even when comparing a script generated from the same database on the same server from two different dates, the order that the objects are scripted will jump around, even for objects that weren't changed at all since the last time they were scripted.

In my case, I'm not particular about how the objects are sorted as long as it is consistent! I want to be able to compare the script from say two months ago and find the one table that had a single column altered in a database containing dozens of tables, stored procedures etc - the proverbial needle in a haystack. A tool like Beyond Compare will do this quite well, but has difficulties when the order of the objects is inconsistent.

Generate Scripts...

From SQL Server Management Studio, I can right click a database, select
Generate Scripts.. and then navigate through a wizard to script my database
objects.
Is it possible to schedule such an activity? I'd like to set up a schedule
to script all my database objects on a regular basis
tia
Terry Holland
Well, you could grab the script it runs using Profiler, and then create a
job that runs the same script.
Or you could just archive backups of the database at the same interval,
assuming size / disk space is not an issue.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Terry Holland" <MSDNNospam248@.nospam.nospam> wrote in message
news:BBDE32F9-FC8F-48CE-9072-AF0BF23F23A0@.microsoft.com...
> From SQL Server Management Studio, I can right click a database, select
> Generate Scripts.. and then navigate through a wizard to script my
> database
> objects.
> Is it possible to schedule such an activity? I'd like to set up a
> schedule
> to script all my database objects on a regular basis
> tia
> Terry Holland
|||Hello,
You will have to use SQL DMO object using sp_OA* extended stored procedures.
Take a look into below URL for details.
http://www.databasejournal.com/features/mssql/article.php/2205291
Thanks
Hari
"Terry Holland" <MSDNNospam248@.nospam.nospam> wrote in message
news:BBDE32F9-FC8F-48CE-9072-AF0BF23F23A0@.microsoft.com...
> From SQL Server Management Studio, I can right click a database, select
> Generate Scripts.. and then navigate through a wizard to script my
> database
> objects.
> Is it possible to schedule such an activity? I'd like to set up a
> schedule
> to script all my database objects on a regular basis
> tia
> Terry Holland
|||You could also have a look here: http://www.elsasoft.org/tools.htm
there you will find a console app that will generate scripts for all
objects in any 2000 or 2005 database. it would be a simple matter to
schedule it to run every day using either Agent or a scheduled task in
windows.
On Apr 4, 6:31 am, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:[vbcol=seagreen]
> Hello,
> You will have to use SQL DMO object using sp_OA* extended stored procedures.
> Take a look into below URL for details.
> http://www.databasejournal.com/features/mssql/article.php/2205291
> Thanks
> Hari
> "Terry Holland" <MSDNNospam...@.nospam.nospam> wrote in message
> news:BBDE32F9-FC8F-48CE-9072-AF0BF23F23A0@.microsoft.com...
>
>
|||Aaron Bertrand [SQL Server MVP] (ten.xoc@.dnartreb.noraa) writes:
> Well, you could grab the script it runs using Profiler, and then create a
> job that runs the same script.
I don't think that would work, since there may be different objects from
day to day.
I would guess the best bet is to see what Integration Services can do. The
script wizard does not use SSIS, but the Copy Database Wizard do, and it
also scripts the database. (Nevermind that CDW is quite flaky.)
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

Generate Script Wizard takes FOREVER (SQL 2k5)

I'm trying to use the Generate Script Wizard to generate scripts for a relatively small DB.

I basically follow the same steps I would have in Sql 2k to generate the scripts but in 2k5 this takes FOREVER.

In fact, it's taking so long that I've stopped it every time (never has completed)

I have sp2 installed.

anyone else having this issue or am I an isolated case ?

basically, from what I can tell, the tool is completely unusable.

Greg Jackson

Portland, OR

I have the same problem with a bigger database with about 1000 sp's and 1000 tables

in sql2000 it took about 15 minutes to script out, now it takes over and hour.

Plus if you watch the server it is hammering the server to 30 to 40 percent utilization

I have broken down the scripting to tables, then script views, then script sp'

It appears that if i change to sql2000 compatible scripts it runs some faster

and if you create drops, and rights it can add another 15 to 20 minutes

Generate Script Wizard takes FOREVER (SQL 2k5)

I'm trying to use the Generate Script Wizard to generate scripts for a relatively small DB.

I basically follow the same steps I would have in Sql 2k to generate the scripts but in 2k5 this takes FOREVER.

In fact, it's taking so long that I've stopped it every time (never has completed)

I have sp2 installed.

anyone else having this issue or am I an isolated case ?

basically, from what I can tell, the tool is completely unusable.

Greg Jackson

Portland, OR

I have the same problem with a bigger database with about 1000 sp's and 1000 tables

in sql2000 it took about 15 minutes to script out, now it takes over and hour.

Plus if you watch the server it is hammering the server to 30 to 40 percent utilization

I have broken down the scripting to tables, then script views, then script sp'

It appears that if i change to sql2000 compatible scripts it runs some faster

and if you create drops, and rights it can add another 15 to 20 minutes

Generate Script Problem

Hi,

When I run the "Generate Scripts.." wizard to generate Stored Procs, they are displayed with all the single quotes replaced by two single quotes and a few additional single quotes here and there. The problem is, I can not take this stored proc and run it in another DB as you can see it from the example below. Do I need to be picking any particular settings?

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[getQuestions]') AND type in (N'P', N'PC'))

BEGIN

EXEC dbo.sp_executesql @.statement = N'

/*************************************************************************************

Stored Proc Desription:

*************************************************************************************/

CREATE PROCEDURE [dbo].[getQuestions]

AS

SELECT a, b, c FROM Country WHERE countrycode=''US''

'

END

The above is not a problem. Please ignore this post. Everything works as intended.