Can someone tell me why I get this behavior?
Whenever I build a new stored procedure into my database I specifically
set the ansi nulls and quoted identifier on like so
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE xxxx AS
It compiles fine, but when I go back to Enterprise Mgr and right click
on the stored procedure and "generate script" it puts
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
before stored proc, and
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
at the end of the script. All my settings in sql server and database
say QUOTED_IDENTIFIER is ON.. SO why does it keep adding
QUOTED_IDENTIFIER OFF to the end of my scripts? I stopped my service to
restarted it.. What could be causing this> restarted it.. What could be causing this
IMO, design miss in EM...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"David" <daveygf@.yahoo.com> wrote in message
news:1108059473.053294.211600@.z14g2000cwz.googlegroups.com...
> Can someone tell me why I get this behavior?
> Whenever I build a new stored procedure into my database I specifically
> set the ansi nulls and quoted identifier on like so
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE PROCEDURE xxxx AS
> It compiles fine, but when I go back to Enterprise Mgr and right click
> on the stored procedure and "generate script" it puts
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> before stored proc, and
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> at the end of the script. All my settings in sql server and database
> say QUOTED_IDENTIFIER is ON.. SO why does it keep adding
> QUOTED_IDENTIFIER OFF to the end of my scripts? I stopped my service to
> restarted it.. What could be causing this
>
Showing posts with label scripts. Show all posts
Showing posts with label scripts. Show all posts
Friday, March 23, 2012
Generating SQL scripts in VS2005
Hi,
I'm using Beta 2 and I've noticed a difference in behavior between VS2005 and VS2003 when generating SQL scripts from database objects in Server Explorer. In VS2003, scripting a table results in separate files (TAB, KEY etc) for the table, indexes and so on. With VS2005 I just get one file.
Is it possible to switch the behavior back to generating mutiple files? If not, why was the behavior changed?
Many thanks in advance!Hi Ian,
Did you ever get an answer to this?
(I've got the same problem!)
Thanks,
Steve|||You should try a different forum. This is an SSIS forum.
Generating SQL scripts for tables
Hi,
Microsoft Enterprise Manager version 8.0 on XP Pro SP2
I am trying to generate an SQL script for the tables in my database, but
when it is setting up constraints in the generated script it is outputting
the following:
ALTER TABLE [dbo].[tblZone] ADD ...
Every other computer in the building outputs this:
ALTER TABLE [dbo].[tblZone] WITH NOCHECK ADD
I am unable to work out why my computer doesn't output the WITH NOCHECK
clause, but it is important for me to work out what is different between my
set up and everyone else's.
Anyone have any ideas?
ThanksOn the options tab under table scripting options is script PRIMARY key,
FOREIGN key, defaults and check constraints selected on the other
computer but not on yours?
http://sqlservercode.blogspot.com/|||Thanks for the reply. I am using the exact same options as everybody else,
and yes, this option is selected.
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1136308864.319858.250050@.g14g2000cwa.googlegroups.com...
> On the options tab under table scripting options is script PRIMARY key,
> FOREIGN key, defaults and check constraints selected on the other
> computer but not on yours?
> http://sqlservercode.blogspot.com/
>
Microsoft Enterprise Manager version 8.0 on XP Pro SP2
I am trying to generate an SQL script for the tables in my database, but
when it is setting up constraints in the generated script it is outputting
the following:
ALTER TABLE [dbo].[tblZone] ADD ...
Every other computer in the building outputs this:
ALTER TABLE [dbo].[tblZone] WITH NOCHECK ADD
I am unable to work out why my computer doesn't output the WITH NOCHECK
clause, but it is important for me to work out what is different between my
set up and everyone else's.
Anyone have any ideas?
ThanksOn the options tab under table scripting options is script PRIMARY key,
FOREIGN key, defaults and check constraints selected on the other
computer but not on yours?
http://sqlservercode.blogspot.com/|||Thanks for the reply. I am using the exact same options as everybody else,
and yes, this option is selected.
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1136308864.319858.250050@.g14g2000cwa.googlegroups.com...
> On the options tab under table scripting options is script PRIMARY key,
> FOREIGN key, defaults and check constraints selected on the other
> computer but not on yours?
> http://sqlservercode.blogspot.com/
>
Generating SQL scripts for tables
Hi,
Microsoft Enterprise Manager version 8.0 on XP Pro SP2
I am trying to generate an SQL script for the tables in my database, but
when it is setting up constraints in the generated script it is outputting
the following:
ALTER TABLE [dbo].[tblZone] ADD ...
Every other computer in the building outputs this:
ALTER TABLE [dbo].[tblZone] WITH NOCHECK ADD
I am unable to work out why my computer doesn't output the WITH NOCHECK
clause, but it is important for me to work out what is different between my
set up and everyone else's.
Anyone have any ideas?
Thanks
On the options tab under table scripting options is script PRIMARY key,
FOREIGN key, defaults and check constraints selected on the other
computer but not on yours?
http://sqlservercode.blogspot.com/
|||Thanks for the reply. I am using the exact same options as everybody else,
and yes, this option is selected.
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1136308864.319858.250050@.g14g2000cwa.googlegr oups.com...
> On the options tab under table scripting options is script PRIMARY key,
> FOREIGN key, defaults and check constraints selected on the other
> computer but not on yours?
> http://sqlservercode.blogspot.com/
>
sql
Microsoft Enterprise Manager version 8.0 on XP Pro SP2
I am trying to generate an SQL script for the tables in my database, but
when it is setting up constraints in the generated script it is outputting
the following:
ALTER TABLE [dbo].[tblZone] ADD ...
Every other computer in the building outputs this:
ALTER TABLE [dbo].[tblZone] WITH NOCHECK ADD
I am unable to work out why my computer doesn't output the WITH NOCHECK
clause, but it is important for me to work out what is different between my
set up and everyone else's.
Anyone have any ideas?
Thanks
On the options tab under table scripting options is script PRIMARY key,
FOREIGN key, defaults and check constraints selected on the other
computer but not on yours?
http://sqlservercode.blogspot.com/
|||Thanks for the reply. I am using the exact same options as everybody else,
and yes, this option is selected.
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1136308864.319858.250050@.g14g2000cwa.googlegr oups.com...
> On the options tab under table scripting options is script PRIMARY key,
> FOREIGN key, defaults and check constraints selected on the other
> computer but not on yours?
> http://sqlservercode.blogspot.com/
>
sql
Generating SQL Scripts
Hi,
How can I generate a script file for all the tables in my database with the data. The script file when run should create all the tables with the constraints and also have the data inserted into them.
Thanks in advance
P.C. VaidyanathanYou can generate the schema easily by going to the Enterprise Manager and right clicking on the dababase name "All Tasks","Generate SQL Scripts". I don't know of anything to script inserting the data. Can you use DTS? Not only will it create the schema and load the data but it will generate all the files used to create the schema.|||I agree with Paul Young, DTS would be your best option to create the tables, load the data nad maybe even do aditional preprocessing to your tables.
This is how we do it on a daily basis with fairly large tables all the time. Realizing size is relative, I am talking about databases as small as a few thousand rows to ones as large as 200 million.
How can I generate a script file for all the tables in my database with the data. The script file when run should create all the tables with the constraints and also have the data inserted into them.
Thanks in advance
P.C. VaidyanathanYou can generate the schema easily by going to the Enterprise Manager and right clicking on the dababase name "All Tasks","Generate SQL Scripts". I don't know of anything to script inserting the data. Can you use DTS? Not only will it create the schema and load the data but it will generate all the files used to create the schema.|||I agree with Paul Young, DTS would be your best option to create the tables, load the data nad maybe even do aditional preprocessing to your tables.
This is how we do it on a daily basis with fairly large tables all the time. Realizing size is relative, I am talking about databases as small as a few thousand rows to ones as large as 200 million.
Generating sql scripts
Hi,
Please guide me in generating sql scripts for table, with the drop
statements in it, from the management studio of sql 2005, Currently only
create statement is generating, and drop statement is not there in it.
Rupesh Mondal.
Make sure you have updated Management Studio till SP2 and configure this (Tools, Options,
Scripting).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
news:D3D0A2E4-87F3-432A-8614-3C146FB95243@.microsoft.com...
> Hi,
> Please guide me in generating sql scripts for table, with the drop
> statements in it, from the management studio of sql 2005, Currently only
> create statement is generating, and drop statement is not there in it.
> Rupesh Mondal.
|||But Mr.Tobor which option to set true in the scripting option, inorder to get
the drop statement in it, before the create statement.Please reply me ASAP
Rupesh Mondal.
"Tibor Karaszi" wrote:
> Make sure you have updated Management Studio till SP2 and configure this (Tools, Options,
> Scripting).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
> news:D3D0A2E4-87F3-432A-8614-3C146FB95243@.microsoft.com...
>
>
Please guide me in generating sql scripts for table, with the drop
statements in it, from the management studio of sql 2005, Currently only
create statement is generating, and drop statement is not there in it.
Rupesh Mondal.
Make sure you have updated Management Studio till SP2 and configure this (Tools, Options,
Scripting).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
news:D3D0A2E4-87F3-432A-8614-3C146FB95243@.microsoft.com...
> Hi,
> Please guide me in generating sql scripts for table, with the drop
> statements in it, from the management studio of sql 2005, Currently only
> create statement is generating, and drop statement is not there in it.
> Rupesh Mondal.
|||But Mr.Tobor which option to set true in the scripting option, inorder to get
the drop statement in it, before the create statement.Please reply me ASAP
Rupesh Mondal.
"Tibor Karaszi" wrote:
> Make sure you have updated Management Studio till SP2 and configure this (Tools, Options,
> Scripting).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
> news:D3D0A2E4-87F3-432A-8614-3C146FB95243@.microsoft.com...
>
>
Generating sql scripts
Hi,
Please guide me in generating sql scripts for table, with the drop
statements in it, from the management studio of sql 2005, Currently only
create statement is generating, and drop statement is not there in it.
Rupesh Mondal.Make sure you have updated Management Studio till SP2 and configure this (Tools, Options,
Scripting).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
news:D3D0A2E4-87F3-432A-8614-3C146FB95243@.microsoft.com...
> Hi,
> Please guide me in generating sql scripts for table, with the drop
> statements in it, from the management studio of sql 2005, Currently only
> create statement is generating, and drop statement is not there in it.
> Rupesh Mondal.|||But Mr.Tobor which option to set true in the scripting option, inorder to get
the drop statement in it, before the create statement.Please reply me ASAP
Rupesh Mondal.
"Tibor Karaszi" wrote:
> Make sure you have updated Management Studio till SP2 and configure this (Tools, Options,
> Scripting).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
> news:D3D0A2E4-87F3-432A-8614-3C146FB95243@.microsoft.com...
> > Hi,
> >
> > Please guide me in generating sql scripts for table, with the drop
> > statements in it, from the management studio of sql 2005, Currently only
> > create statement is generating, and drop statement is not there in it.
> >
> > Rupesh Mondal.
>
>|||Oops, by error. I was certain this option was there but it seems it isn't. The closest one I can
find is the "Include IF NOT EXIST clause" which is a bit different.
I think this option is available at the SMO level, but that would require you to code your own
generate script application.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
news:FBD71B40-7CB5-49C5-B9E8-6DD298E4385D@.microsoft.com...
> But Mr.Tobor which option to set true in the scripting option, inorder to get
> the drop statement in it, before the create statement.Please reply me ASAP
> Rupesh Mondal.
> "Tibor Karaszi" wrote:
>> Make sure you have updated Management Studio till SP2 and configure this (Tools, Options,
>> Scripting).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
>> news:D3D0A2E4-87F3-432A-8614-3C146FB95243@.microsoft.com...
>> > Hi,
>> >
>> > Please guide me in generating sql scripts for table, with the drop
>> > statements in it, from the management studio of sql 2005, Currently only
>> > create statement is generating, and drop statement is not there in it.
>> >
>> > Rupesh Mondal.
>>|||here's an open source app that will generate scripts - it would be a
starting point for you if you go that route:
http://www.codeplex.com/scriptdb
On Apr 19, 12:59 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Oops, by error. I was certain this option was there but it seems it isn't. The closest one I can
> find is the "Include IF NOT EXIST clause" which is a bit different.
> I think this option is available at the SMO level, but that would require you to code your owngeneratescriptapplication.
> --
> Tibor Karaszi,SQLServer MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Rupesh Mondal" <RupeshMon...@.discussions.microsoft.com> wrote in message
> news:FBD71B40-7CB5-49C5-B9E8-6DD298E4385D@.microsoft.com...
> > But Mr.Tobor which option to set true in the scripting option, inorder to get
> > the drop statement in it, before the create statement.Please reply me ASAP
> > Rupesh Mondal.
> > "Tibor Karaszi" wrote:
> >> Make sure you have updated Management Studio till SP2 and configure this (Tools, Options,
> >> Scripting).
> >> --
> >> Tibor Karaszi,SQLServer MVP
> >>http://www.karaszi.com/sqlserver/default.asp
> >>http://sqlblog.com/blogs/tibor_karaszi
> >> "Rupesh Mondal" <RupeshMon...@.discussions.microsoft.com> wrote in message
> >>news:D3D0A2E4-87F3-432A-8614-3C146FB95243@.microsoft.com...
> >> > Hi,
> >> > Please guide me in generatingsqlscripts for table, with the drop
> >> > statements in it, from the management studio ofsql2005, Currently only
> >> > create statement is generating, and drop statement is not there in it.
> >> > Rupesh Mondal.
Please guide me in generating sql scripts for table, with the drop
statements in it, from the management studio of sql 2005, Currently only
create statement is generating, and drop statement is not there in it.
Rupesh Mondal.Make sure you have updated Management Studio till SP2 and configure this (Tools, Options,
Scripting).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
news:D3D0A2E4-87F3-432A-8614-3C146FB95243@.microsoft.com...
> Hi,
> Please guide me in generating sql scripts for table, with the drop
> statements in it, from the management studio of sql 2005, Currently only
> create statement is generating, and drop statement is not there in it.
> Rupesh Mondal.|||But Mr.Tobor which option to set true in the scripting option, inorder to get
the drop statement in it, before the create statement.Please reply me ASAP
Rupesh Mondal.
"Tibor Karaszi" wrote:
> Make sure you have updated Management Studio till SP2 and configure this (Tools, Options,
> Scripting).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
> news:D3D0A2E4-87F3-432A-8614-3C146FB95243@.microsoft.com...
> > Hi,
> >
> > Please guide me in generating sql scripts for table, with the drop
> > statements in it, from the management studio of sql 2005, Currently only
> > create statement is generating, and drop statement is not there in it.
> >
> > Rupesh Mondal.
>
>|||Oops, by error. I was certain this option was there but it seems it isn't. The closest one I can
find is the "Include IF NOT EXIST clause" which is a bit different.
I think this option is available at the SMO level, but that would require you to code your own
generate script application.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
news:FBD71B40-7CB5-49C5-B9E8-6DD298E4385D@.microsoft.com...
> But Mr.Tobor which option to set true in the scripting option, inorder to get
> the drop statement in it, before the create statement.Please reply me ASAP
> Rupesh Mondal.
> "Tibor Karaszi" wrote:
>> Make sure you have updated Management Studio till SP2 and configure this (Tools, Options,
>> Scripting).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
>> news:D3D0A2E4-87F3-432A-8614-3C146FB95243@.microsoft.com...
>> > Hi,
>> >
>> > Please guide me in generating sql scripts for table, with the drop
>> > statements in it, from the management studio of sql 2005, Currently only
>> > create statement is generating, and drop statement is not there in it.
>> >
>> > Rupesh Mondal.
>>|||here's an open source app that will generate scripts - it would be a
starting point for you if you go that route:
http://www.codeplex.com/scriptdb
On Apr 19, 12:59 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Oops, by error. I was certain this option was there but it seems it isn't. The closest one I can
> find is the "Include IF NOT EXIST clause" which is a bit different.
> I think this option is available at the SMO level, but that would require you to code your owngeneratescriptapplication.
> --
> Tibor Karaszi,SQLServer MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Rupesh Mondal" <RupeshMon...@.discussions.microsoft.com> wrote in message
> news:FBD71B40-7CB5-49C5-B9E8-6DD298E4385D@.microsoft.com...
> > But Mr.Tobor which option to set true in the scripting option, inorder to get
> > the drop statement in it, before the create statement.Please reply me ASAP
> > Rupesh Mondal.
> > "Tibor Karaszi" wrote:
> >> Make sure you have updated Management Studio till SP2 and configure this (Tools, Options,
> >> Scripting).
> >> --
> >> Tibor Karaszi,SQLServer MVP
> >>http://www.karaszi.com/sqlserver/default.asp
> >>http://sqlblog.com/blogs/tibor_karaszi
> >> "Rupesh Mondal" <RupeshMon...@.discussions.microsoft.com> wrote in message
> >>news:D3D0A2E4-87F3-432A-8614-3C146FB95243@.microsoft.com...
> >> > Hi,
> >> > Please guide me in generatingsqlscripts for table, with the drop
> >> > statements in it, from the management studio ofsql2005, Currently only
> >> > create statement is generating, and drop statement is not there in it.
> >> > Rupesh Mondal.
Generating scripts, tables, views, procedures, roles...
Hi...Database Publishing Wizard
|||Thomas, did you find a solution to your problem?
I'm trying to generate scripts in SQL Server Management Studio 2005.
When I choose 'Script all objects' I get an error when I try to execute it. When I generate the scripts in single files, only tables in one file, only views in one file etc. etc., the execution is succeded.
1) Why do I get an error when I try to execute the script containing tables, views, procedures in one file....?
2) I get an error in the view-file where one column is 'invalid' but I can see it in the view. A generated script should execute succesfully when it is generated one second ago on the same database and so on... Right?
Thanks....
Which exact error do you get ?
Jens K. Suessmeyer
http://www.sqlserver2005.de
sorry, I cant reproduce the errors because my workbuddy has the day off...
But I remember it was one of the views with 2 columns in it, which caused the error: 'Invalid item on view...'
And the sequense in the script is wrong. For example I can see that the script are trying to create a view before the table is created?
|||You may find that the Database Publishing Wizard offers you more functionality.
Database Publishing Wizard
http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en
|||Thomas, did you find a solution to your problem?I have the same problem and Database Publishing Wizard does not work any better.
The CREATE VIEW is placed before the dependant tables in the script.
This ONLY happens when I'm trying to script a SQL Server 2005 Express db.
I run the script wizard in SQL Server Management Studio 2005.
SQL Server 2005 Express: 9.0.3042
SQL Server 2005: 9.0.1399
Thanks
Jonas
|||
Did you use the switch "create dependent objects" ?
Jens K. Suessmeyer
http://www.sqlserver2005.de
|||Yes I did.
I've now updated to sp2 (9.0.3042) on the SQL Server 2005 too - no difference. Except that the default value of "create dependant objects" in the script wizard is now false.
I can not include any image of the properties selected, but all are default exept the dependant switch which is true.
The final report start with:
Generate Script Progress
- Determining objects in database '....MDF' that will be scripted. (Success)
- dbo.AllDataView (Success)
- dbo.FindNewRawData (Success)
Which also shows that a View is created first of all objects.
Thanks
Jonas
Generating scripts, tables, views, procedures, roles...
Hi...Database Publishing Wizard
|||Thomas, did you find a solution to your problem?
I'm trying to generate scripts in SQL Server Management Studio 2005.
When I choose 'Script all objects' I get an error when I try to execute it. When I generate the scripts in single files, only tables in one file, only views in one file etc. etc., the execution is succeded.
1) Why do I get an error when I try to execute the script containing tables, views, procedures in one file....?
2) I get an error in the view-file where one column is 'invalid' but I can see it in the view. A generated script should execute succesfully when it is generated one second ago on the same database and so on... Right?
Thanks....
Which exact error do you get ?
Jens K. Suessmeyer
http://www.sqlserver2005.de
sorry, I cant reproduce the errors because my workbuddy has the day off...
But I remember it was one of the views with 2 columns in it, which caused the error: 'Invalid item on view...'
And the sequense in the script is wrong. For example I can see that the script are trying to create a view before the table is created?
|||You may find that the Database Publishing Wizard offers you more functionality.
Database Publishing Wizard
http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en
|||Thomas, did you find a solution to your problem?I have the same problem and Database Publishing Wizard does not work any better.
The CREATE VIEW is placed before the dependant tables in the script.
This ONLY happens when I'm trying to script a SQL Server 2005 Express db.
I run the script wizard in SQL Server Management Studio 2005.
SQL Server 2005 Express: 9.0.3042
SQL Server 2005: 9.0.1399
Thanks
Jonas
|||
Did you use the switch "create dependent objects" ?
Jens K. Suessmeyer
http://www.sqlserver2005.de
|||Yes I did.
I've now updated to sp2 (9.0.3042) on the SQL Server 2005 too - no difference. Except that the default value of "create dependant objects" in the script wizard is now false.
I can not include any image of the properties selected, but all are default exept the dependant switch which is true.
The final report start with:
Generate Script Progress
- Determining objects in database '....MDF' that will be scripted. (Success)
- dbo.AllDataView (Success)
- dbo.FindNewRawData (Success)
Which also shows that a View is created first of all objects.
Thanks
Jonas
Generating scripts, tables, views, procedures, roles...
Hi...Database Publishing Wizard
I'm trying to generate scripts in SQL Server Management Studio 2005.
When I choose 'Script all objects' I get an error when I try to execute it. When I generate the scripts in single files, only tables in one file, only views in one file etc. etc., the execution is succeded.
1) Why do I get an error when I try to execute the script containing tables, views, procedures in one file....?
2) I get an error in the view-file where one column is 'invalid' but I can see it in the view. A generated script should execute succesfully when it is generated one second ago on the same database and so on... Right?
Thanks....
Which exact error do you get ?
Jens K. Suessmeyer
http://www.sqlserver2005.de
sorry, I cant reproduce the errors because my workbuddy has the day off...
But I remember it was one of the views with 2 columns in it, which caused the error: 'Invalid item on view...'
And the sequense in the script is wrong. For example I can see that the script are trying to create a view before the table is created?
|||
You may find that the Database Publishing Wizard offers you more functionality.
Database Publishing Wizard
http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en
|||Thomas, did you find a solution to your problem?
I have the same problem and Database Publishing Wizard does not work any better.
The CREATE VIEW is placed before the dependant tables in the script.
This ONLY happens when I'm trying to script a SQL Server 2005 Express db.
I run the script wizard in SQL Server Management Studio 2005.
SQL Server 2005 Express: 9.0.3042
SQL Server 2005: 9.0.1399
Thanks
Jonas
|||
Did you use the switch "create dependent objects" ?
Jens K. Suessmeyer
http://www.sqlserver2005.de
|||Yes I did.
I've now updated to sp2 (9.0.3042) on the SQL Server 2005 too - no difference. Except that the default value of "create dependant objects" in the script wizard is now false.
I can not include any image of the properties selected, but all are default exept the dependant switch which is true.
The final report start with:
Generate Script Progress
- Determining objects in database '....MDF' that will be scripted. (Success)
- dbo.AllDataView (Success)
- dbo.FindNewRawData (Success)
Which also shows that a View is created first of all objects.
Thanks
Jonas
Generating Scripts to Populate Tables
I would like to find a utility that can scan through an existing Sql
Server 2000 database and create scripts to re-insert/re-populate the
data into another DB with the same table structures. Like a back-up
utility.
Can anyone recommend one (or tell me which ones to avoid)? I
would like one that takes dependencies between tables into
consideration.
Server 2000 database and create scripts to re-insert/re-populate the
data into another DB with the same table structures. Like a back-up
utility.
Can anyone recommend one (or tell me which ones to avoid)? I
would like one that takes dependencies between tables into
consideration.
ThanksTry this stored procedure:
http://vyaskn.tripod.com/code.htm#inserts
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Chris Jones" <sarophes@.yahoo.com> wrote in message
news:ad3e5184.0408110953.4a2c4c20@.posting.google.c om...
> I would like to find a utility that can scan through an existing Sql
> Server 2000 database and create scripts to re-insert/re-populate the
> data into another DB with the same table structures. Like a back-up
> utility.
> Can anyone recommend one (or tell me which ones to avoid)? I
> would like one that takes dependencies between tables into
> consideration.
> Thanks
generating scripts in SqlServer 2005?
Okay, in Sql 2000, when I wanted to generate scripts, I could easily have
both the create and drop commands scripted into the same document with one
step. How do I do that with Sql 2005. I can generate the script for
creating the table and for dropping the table, but I cannot see the flag
where I can have both scripted at the same time.> step. How do I do that with Sql 2005.
Please use the SQL Server 2005 newsgroups.
http://www.aspfaq.com/sql2005/show.asp?id=1sql
both the create and drop commands scripted into the same document with one
step. How do I do that with Sql 2005. I can generate the script for
creating the table and for dropping the table, but I cannot see the flag
where I can have both scripted at the same time.> step. How do I do that with Sql 2005.
Please use the SQL Server 2005 newsgroups.
http://www.aspfaq.com/sql2005/show.asp?id=1sql
Generating scripts in SQL 2005
In SQL 2000 EM you were able to generate a script that only contained GRANT statements, (by unchecking the CREATE & DROP commands on the 2nd tab, and selecting Object Permissions on the 3rd tab). From what I can see in SQL 2005 MS you can't do this.
All that you can seem to do is generate a sciprt containing all the CREATE statements with the GRANT statement(s) after each object.
This is a bit of a pain as I want to script all the object permissions, without having to wade through hundreds of CREATE TABLE and CREATE PROCEDURE statements.
How can get just a script of GRANT statements?
Thanks.
It is not possible to generate scripts for object permissions through Management Studio like you could in Enterprise Manager. However, you can write a small SMO program to get the information the you need. This example should get you started on how to generate GRANT scripts for stored procedures.
ObjectPermissionInfo[] a = new ObjectPermissionInfo[5];
Database db = srv.Databases["pubs"];
foreach (StoredProcedure sp in db.StoredProcedures)
{
a = sp.EnumObjectPermissions();
foreach (ObjectPermissionInfo op in a)
{
Console.WriteLine("GRANT " + op.PermissionType.ToString() + " ON " + op.ObjectName.ToString() + " TO " + op.Grantee.ToString());
}
}
~Peter
|||Thanks for answering, I'll try what you suggest.Generating scripts in Management Studio
I'm using Management Studio to manage and interface with our Sql
Server 2000 servers. One thing that doesn't (to me) appear to work as
well is the object scripting. I know they deprecated things from EM
2000, that are now back in via SP2, but I'm not finding the useful
tools I previously enjoyed.
One thing I am having problems with is that the scripting behavior
performs AWFULLY. When I simply want to script an object (trigger,
table, etc.) to a new query editor window (like I previously could in
QA), it may take upwards of 30 seconds before what ends up being a
very short script is output to the query window. Why?
Unrelated to scripting, if my DB has a substantial number of stored
procs, it takes a LONG time for the list to appear. In EM / QA, it
would appear instantly.
Also, we used to be able to easily generate DROP statements at the top
of every script (table, trigger, proc, view, function, etc.). The only
way I see to do this, now, is to use the script wizard on the
database. If I want to quickly generate a single script, I don't want
to use a freaking wizard. Grrrr...
Finally, how can I get my table scripts to stop generating column-
level permissions? If an account has full access to a table, I really
don't care about the column-by-column access. I'm wondering if 'script
object level permissions' is adding to the LONG time it takes to
generate a script.
If anyone has tips on how to optimize the production of scripts,
please share. At this point, I kind of wish I had stayed with EM /
QA...
Ryan
> One thing I am having problems with is that the scripting behavior
> performs AWFULLY. When I simply want to script an object (trigger,
> table, etc.) to a new query editor window (like I previously could in
> QA), it may take upwards of 30 seconds before what ends up being a
> very short script is output to the query window. Why?
Object scripting in general is a poor performer. But I have only really
noticed this when scripting a lot of objects. For single objects this seems
to be pretty quick. To be honest though, my habit for most code-based
objects is to right-click, modify rather than script as > ... less clicks.
When I need to generate scripts for many objects, I don't use EM or SSMS at
all... I use SQL Compare from red-gate (there are other similarly capable
tools, e.g. Apex SQL diff, and a free one from Quest). Just compare your
database against an empty database and you will get a set of objects you can
script or nor script using checkboxes.
It is extremely fast and the only issue I've ever had is trying to create an
indexed view and stored procedure referencing it in the wrong order. My
solution to not running into that issue has been to script views/tables
first, then procedures/functions. A lot of other options are there too,
such as temporarily disabling DDL triggers.
> Unrelated to scripting, if my DB has a substantial number of stored
> procs, it takes a LONG time for the list to appear. In EM / QA, it
> would appear instantly.
As a test, I have a server in our data center with 400+ databases. Each
database has 354 stored procedures. Over a VPN I can repeatedly pick any
random database and expand the stored procedure list in about a second. And
this is a production system (so it's not just a set of empty databases with
no activity).
If you're not talking about stored procedure counts that are higher by an
order of magnitude or more, this may be a machine resource issue. Perhaps
in EM/QA this list would have appeared even faster for me. :-)
Aaron Bertrand
SQL Server MVP
|||Could your complaints be related to the INITIAL attempt to perform
something - in which case it is just all the frickin' .NET crap having to be
loaded/initialized.
TheSQLGuru
President
Indicium Resources, Inc.
"herlihyboy" <ryan.parmenter@.gmail.com> wrote in message
news:1184946663.951045.202120@.g4g2000hsf.googlegro ups.com...
> I'm using Management Studio to manage and interface with our Sql
> Server 2000 servers. One thing that doesn't (to me) appear to work as
> well is the object scripting. I know they deprecated things from EM
> 2000, that are now back in via SP2, but I'm not finding the useful
> tools I previously enjoyed.
> One thing I am having problems with is that the scripting behavior
> performs AWFULLY. When I simply want to script an object (trigger,
> table, etc.) to a new query editor window (like I previously could in
> QA), it may take upwards of 30 seconds before what ends up being a
> very short script is output to the query window. Why?
> Unrelated to scripting, if my DB has a substantial number of stored
> procs, it takes a LONG time for the list to appear. In EM / QA, it
> would appear instantly.
> Also, we used to be able to easily generate DROP statements at the top
> of every script (table, trigger, proc, view, function, etc.). The only
> way I see to do this, now, is to use the script wizard on the
> database. If I want to quickly generate a single script, I don't want
> to use a freaking wizard. Grrrr...
> Finally, how can I get my table scripts to stop generating column-
> level permissions? If an account has full access to a table, I really
> don't care about the column-by-column access. I'm wondering if 'script
> object level permissions' is adding to the LONG time it takes to
> generate a script.
> If anyone has tips on how to optimize the production of scripts,
> please share. At this point, I kind of wish I had stayed with EM /
> QA...
> Ryan
>
|||Probably not, I was scripting 300+ SPs and was watching the progress in the
script dialog to script them one by one, it was like molasses on a cold day.
Roman
Roman Rehak
http://sqlblog.com/blogs/roman_rehak
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:esXxMKvyHHA.2172@.TK2MSFTNGP06.phx.gbl...
> Could your complaints be related to the INITIAL attempt to perform
> something - in which case it is just all the frickin' .NET crap having to
> be loaded/initialized.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "herlihyboy" <ryan.parmenter@.gmail.com> wrote in message
> news:1184946663.951045.202120@.g4g2000hsf.googlegro ups.com...
>
Server 2000 servers. One thing that doesn't (to me) appear to work as
well is the object scripting. I know they deprecated things from EM
2000, that are now back in via SP2, but I'm not finding the useful
tools I previously enjoyed.
One thing I am having problems with is that the scripting behavior
performs AWFULLY. When I simply want to script an object (trigger,
table, etc.) to a new query editor window (like I previously could in
QA), it may take upwards of 30 seconds before what ends up being a
very short script is output to the query window. Why?
Unrelated to scripting, if my DB has a substantial number of stored
procs, it takes a LONG time for the list to appear. In EM / QA, it
would appear instantly.
Also, we used to be able to easily generate DROP statements at the top
of every script (table, trigger, proc, view, function, etc.). The only
way I see to do this, now, is to use the script wizard on the
database. If I want to quickly generate a single script, I don't want
to use a freaking wizard. Grrrr...
Finally, how can I get my table scripts to stop generating column-
level permissions? If an account has full access to a table, I really
don't care about the column-by-column access. I'm wondering if 'script
object level permissions' is adding to the LONG time it takes to
generate a script.
If anyone has tips on how to optimize the production of scripts,
please share. At this point, I kind of wish I had stayed with EM /
QA...
Ryan
> One thing I am having problems with is that the scripting behavior
> performs AWFULLY. When I simply want to script an object (trigger,
> table, etc.) to a new query editor window (like I previously could in
> QA), it may take upwards of 30 seconds before what ends up being a
> very short script is output to the query window. Why?
Object scripting in general is a poor performer. But I have only really
noticed this when scripting a lot of objects. For single objects this seems
to be pretty quick. To be honest though, my habit for most code-based
objects is to right-click, modify rather than script as > ... less clicks.
When I need to generate scripts for many objects, I don't use EM or SSMS at
all... I use SQL Compare from red-gate (there are other similarly capable
tools, e.g. Apex SQL diff, and a free one from Quest). Just compare your
database against an empty database and you will get a set of objects you can
script or nor script using checkboxes.
It is extremely fast and the only issue I've ever had is trying to create an
indexed view and stored procedure referencing it in the wrong order. My
solution to not running into that issue has been to script views/tables
first, then procedures/functions. A lot of other options are there too,
such as temporarily disabling DDL triggers.
> Unrelated to scripting, if my DB has a substantial number of stored
> procs, it takes a LONG time for the list to appear. In EM / QA, it
> would appear instantly.
As a test, I have a server in our data center with 400+ databases. Each
database has 354 stored procedures. Over a VPN I can repeatedly pick any
random database and expand the stored procedure list in about a second. And
this is a production system (so it's not just a set of empty databases with
no activity).
If you're not talking about stored procedure counts that are higher by an
order of magnitude or more, this may be a machine resource issue. Perhaps
in EM/QA this list would have appeared even faster for me. :-)
Aaron Bertrand
SQL Server MVP
|||Could your complaints be related to the INITIAL attempt to perform
something - in which case it is just all the frickin' .NET crap having to be
loaded/initialized.
TheSQLGuru
President
Indicium Resources, Inc.
"herlihyboy" <ryan.parmenter@.gmail.com> wrote in message
news:1184946663.951045.202120@.g4g2000hsf.googlegro ups.com...
> I'm using Management Studio to manage and interface with our Sql
> Server 2000 servers. One thing that doesn't (to me) appear to work as
> well is the object scripting. I know they deprecated things from EM
> 2000, that are now back in via SP2, but I'm not finding the useful
> tools I previously enjoyed.
> One thing I am having problems with is that the scripting behavior
> performs AWFULLY. When I simply want to script an object (trigger,
> table, etc.) to a new query editor window (like I previously could in
> QA), it may take upwards of 30 seconds before what ends up being a
> very short script is output to the query window. Why?
> Unrelated to scripting, if my DB has a substantial number of stored
> procs, it takes a LONG time for the list to appear. In EM / QA, it
> would appear instantly.
> Also, we used to be able to easily generate DROP statements at the top
> of every script (table, trigger, proc, view, function, etc.). The only
> way I see to do this, now, is to use the script wizard on the
> database. If I want to quickly generate a single script, I don't want
> to use a freaking wizard. Grrrr...
> Finally, how can I get my table scripts to stop generating column-
> level permissions? If an account has full access to a table, I really
> don't care about the column-by-column access. I'm wondering if 'script
> object level permissions' is adding to the LONG time it takes to
> generate a script.
> If anyone has tips on how to optimize the production of scripts,
> please share. At this point, I kind of wish I had stayed with EM /
> QA...
> Ryan
>
|||Probably not, I was scripting 300+ SPs and was watching the progress in the
script dialog to script them one by one, it was like molasses on a cold day.
Roman
Roman Rehak
http://sqlblog.com/blogs/roman_rehak
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:esXxMKvyHHA.2172@.TK2MSFTNGP06.phx.gbl...
> Could your complaints be related to the INITIAL attempt to perform
> something - in which case it is just all the frickin' .NET crap having to
> be loaded/initialized.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "herlihyboy" <ryan.parmenter@.gmail.com> wrote in message
> news:1184946663.951045.202120@.g4g2000hsf.googlegro ups.com...
>
Generating scripts in Management Studio
I'm using Management Studio to manage and interface with our Sql
Server 2000 servers. One thing that doesn't (to me) appear to work as
well is the object scripting. I know they deprecated things from EM
2000, that are now back in via SP2, but I'm not finding the useful
tools I previously enjoyed.
One thing I am having problems with is that the scripting behavior
performs AWFULLY. When I simply want to script an object (trigger,
table, etc.) to a new query editor window (like I previously could in
QA), it may take upwards of 30 seconds before what ends up being a
very short script is output to the query window. Why?
Unrelated to scripting, if my DB has a substantial number of stored
procs, it takes a LONG time for the list to appear. In EM / QA, it
would appear instantly.
Also, we used to be able to easily generate DROP statements at the top
of every script (table, trigger, proc, view, function, etc.). The only
way I see to do this, now, is to use the script wizard on the
database. If I want to quickly generate a single script, I don't want
to use a freaking wizard. Grrrr...
Finally, how can I get my table scripts to stop generating column-
level permissions? If an account has full access to a table, I really
don't care about the column-by-column access. I'm wondering if 'script
object level permissions' is adding to the LONG time it takes to
generate a script.
If anyone has tips on how to optimize the production of scripts,
please share. At this point, I kind of wish I had stayed with EM /
QA...
Ryan> One thing I am having problems with is that the scripting behavior
> performs AWFULLY. When I simply want to script an object (trigger,
> table, etc.) to a new query editor window (like I previously could in
> QA), it may take upwards of 30 seconds before what ends up being a
> very short script is output to the query window. Why?
Object scripting in general is a poor performer. But I have only really
noticed this when scripting a lot of objects. For single objects this seems
to be pretty quick. To be honest though, my habit for most code-based
objects is to right-click, modify rather than script as > ... less clicks.
When I need to generate scripts for many objects, I don't use EM or SSMS at
all... I use SQL Compare from red-gate (there are other similarly capable
tools, e.g. Apex SQL diff, and a free one from Quest). Just compare your
database against an empty database and you will get a set of objects you can
script or nor script using checkboxes.
It is extremely fast and the only issue I've ever had is trying to create an
indexed view and stored procedure referencing it in the wrong order. My
solution to not running into that issue has been to script views/tables
first, then procedures/functions. A lot of other options are there too,
such as temporarily disabling DDL triggers.
> Unrelated to scripting, if my DB has a substantial number of stored
> procs, it takes a LONG time for the list to appear. In EM / QA, it
> would appear instantly.
As a test, I have a server in our data center with 400+ databases. Each
database has 354 stored procedures. Over a VPN I can repeatedly pick any
random database and expand the stored procedure list in about a second. And
this is a production system (so it's not just a set of empty databases with
no activity).
If you're not talking about stored procedure counts that are higher by an
order of magnitude or more, this may be a machine resource issue. Perhaps
in EM/QA this list would have appeared even faster for me. :-)
--
Aaron Bertrand
SQL Server MVP|||Could your complaints be related to the INITIAL attempt to perform
something - in which case it is just all the frickin' .NET crap having to be
loaded/initialized.
--
TheSQLGuru
President
Indicium Resources, Inc.
"herlihyboy" <ryan.parmenter@.gmail.com> wrote in message
news:1184946663.951045.202120@.g4g2000hsf.googlegroups.com...
> I'm using Management Studio to manage and interface with our Sql
> Server 2000 servers. One thing that doesn't (to me) appear to work as
> well is the object scripting. I know they deprecated things from EM
> 2000, that are now back in via SP2, but I'm not finding the useful
> tools I previously enjoyed.
> One thing I am having problems with is that the scripting behavior
> performs AWFULLY. When I simply want to script an object (trigger,
> table, etc.) to a new query editor window (like I previously could in
> QA), it may take upwards of 30 seconds before what ends up being a
> very short script is output to the query window. Why?
> Unrelated to scripting, if my DB has a substantial number of stored
> procs, it takes a LONG time for the list to appear. In EM / QA, it
> would appear instantly.
> Also, we used to be able to easily generate DROP statements at the top
> of every script (table, trigger, proc, view, function, etc.). The only
> way I see to do this, now, is to use the script wizard on the
> database. If I want to quickly generate a single script, I don't want
> to use a freaking wizard. Grrrr...
> Finally, how can I get my table scripts to stop generating column-
> level permissions? If an account has full access to a table, I really
> don't care about the column-by-column access. I'm wondering if 'script
> object level permissions' is adding to the LONG time it takes to
> generate a script.
> If anyone has tips on how to optimize the production of scripts,
> please share. At this point, I kind of wish I had stayed with EM /
> QA...
> Ryan
>|||Probably not, I was scripting 300+ SPs and was watching the progress in the
script dialog to script them one by one, it was like molasses on a cold day.
Roman
--
Roman Rehak
http://sqlblog.com/blogs/roman_rehak
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:esXxMKvyHHA.2172@.TK2MSFTNGP06.phx.gbl...
> Could your complaints be related to the INITIAL attempt to perform
> something - in which case it is just all the frickin' .NET crap having to
> be loaded/initialized.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "herlihyboy" <ryan.parmenter@.gmail.com> wrote in message
> news:1184946663.951045.202120@.g4g2000hsf.googlegroups.com...
>> I'm using Management Studio to manage and interface with our Sql
>> Server 2000 servers. One thing that doesn't (to me) appear to work as
>> well is the object scripting. I know they deprecated things from EM
>> 2000, that are now back in via SP2, but I'm not finding the useful
>> tools I previously enjoyed.
>> One thing I am having problems with is that the scripting behavior
>> performs AWFULLY. When I simply want to script an object (trigger,
>> table, etc.) to a new query editor window (like I previously could in
>> QA), it may take upwards of 30 seconds before what ends up being a
>> very short script is output to the query window. Why?
>> Unrelated to scripting, if my DB has a substantial number of stored
>> procs, it takes a LONG time for the list to appear. In EM / QA, it
>> would appear instantly.
>> Also, we used to be able to easily generate DROP statements at the top
>> of every script (table, trigger, proc, view, function, etc.). The only
>> way I see to do this, now, is to use the script wizard on the
>> database. If I want to quickly generate a single script, I don't want
>> to use a freaking wizard. Grrrr...
>> Finally, how can I get my table scripts to stop generating column-
>> level permissions? If an account has full access to a table, I really
>> don't care about the column-by-column access. I'm wondering if 'script
>> object level permissions' is adding to the LONG time it takes to
>> generate a script.
>> If anyone has tips on how to optimize the production of scripts,
>> please share. At this point, I kind of wish I had stayed with EM /
>> QA...
>> Ryan
>
Server 2000 servers. One thing that doesn't (to me) appear to work as
well is the object scripting. I know they deprecated things from EM
2000, that are now back in via SP2, but I'm not finding the useful
tools I previously enjoyed.
One thing I am having problems with is that the scripting behavior
performs AWFULLY. When I simply want to script an object (trigger,
table, etc.) to a new query editor window (like I previously could in
QA), it may take upwards of 30 seconds before what ends up being a
very short script is output to the query window. Why?
Unrelated to scripting, if my DB has a substantial number of stored
procs, it takes a LONG time for the list to appear. In EM / QA, it
would appear instantly.
Also, we used to be able to easily generate DROP statements at the top
of every script (table, trigger, proc, view, function, etc.). The only
way I see to do this, now, is to use the script wizard on the
database. If I want to quickly generate a single script, I don't want
to use a freaking wizard. Grrrr...
Finally, how can I get my table scripts to stop generating column-
level permissions? If an account has full access to a table, I really
don't care about the column-by-column access. I'm wondering if 'script
object level permissions' is adding to the LONG time it takes to
generate a script.
If anyone has tips on how to optimize the production of scripts,
please share. At this point, I kind of wish I had stayed with EM /
QA...
Ryan> One thing I am having problems with is that the scripting behavior
> performs AWFULLY. When I simply want to script an object (trigger,
> table, etc.) to a new query editor window (like I previously could in
> QA), it may take upwards of 30 seconds before what ends up being a
> very short script is output to the query window. Why?
Object scripting in general is a poor performer. But I have only really
noticed this when scripting a lot of objects. For single objects this seems
to be pretty quick. To be honest though, my habit for most code-based
objects is to right-click, modify rather than script as > ... less clicks.
When I need to generate scripts for many objects, I don't use EM or SSMS at
all... I use SQL Compare from red-gate (there are other similarly capable
tools, e.g. Apex SQL diff, and a free one from Quest). Just compare your
database against an empty database and you will get a set of objects you can
script or nor script using checkboxes.
It is extremely fast and the only issue I've ever had is trying to create an
indexed view and stored procedure referencing it in the wrong order. My
solution to not running into that issue has been to script views/tables
first, then procedures/functions. A lot of other options are there too,
such as temporarily disabling DDL triggers.
> Unrelated to scripting, if my DB has a substantial number of stored
> procs, it takes a LONG time for the list to appear. In EM / QA, it
> would appear instantly.
As a test, I have a server in our data center with 400+ databases. Each
database has 354 stored procedures. Over a VPN I can repeatedly pick any
random database and expand the stored procedure list in about a second. And
this is a production system (so it's not just a set of empty databases with
no activity).
If you're not talking about stored procedure counts that are higher by an
order of magnitude or more, this may be a machine resource issue. Perhaps
in EM/QA this list would have appeared even faster for me. :-)
--
Aaron Bertrand
SQL Server MVP|||Could your complaints be related to the INITIAL attempt to perform
something - in which case it is just all the frickin' .NET crap having to be
loaded/initialized.
--
TheSQLGuru
President
Indicium Resources, Inc.
"herlihyboy" <ryan.parmenter@.gmail.com> wrote in message
news:1184946663.951045.202120@.g4g2000hsf.googlegroups.com...
> I'm using Management Studio to manage and interface with our Sql
> Server 2000 servers. One thing that doesn't (to me) appear to work as
> well is the object scripting. I know they deprecated things from EM
> 2000, that are now back in via SP2, but I'm not finding the useful
> tools I previously enjoyed.
> One thing I am having problems with is that the scripting behavior
> performs AWFULLY. When I simply want to script an object (trigger,
> table, etc.) to a new query editor window (like I previously could in
> QA), it may take upwards of 30 seconds before what ends up being a
> very short script is output to the query window. Why?
> Unrelated to scripting, if my DB has a substantial number of stored
> procs, it takes a LONG time for the list to appear. In EM / QA, it
> would appear instantly.
> Also, we used to be able to easily generate DROP statements at the top
> of every script (table, trigger, proc, view, function, etc.). The only
> way I see to do this, now, is to use the script wizard on the
> database. If I want to quickly generate a single script, I don't want
> to use a freaking wizard. Grrrr...
> Finally, how can I get my table scripts to stop generating column-
> level permissions? If an account has full access to a table, I really
> don't care about the column-by-column access. I'm wondering if 'script
> object level permissions' is adding to the LONG time it takes to
> generate a script.
> If anyone has tips on how to optimize the production of scripts,
> please share. At this point, I kind of wish I had stayed with EM /
> QA...
> Ryan
>|||Probably not, I was scripting 300+ SPs and was watching the progress in the
script dialog to script them one by one, it was like molasses on a cold day.
Roman
--
Roman Rehak
http://sqlblog.com/blogs/roman_rehak
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:esXxMKvyHHA.2172@.TK2MSFTNGP06.phx.gbl...
> Could your complaints be related to the INITIAL attempt to perform
> something - in which case it is just all the frickin' .NET crap having to
> be loaded/initialized.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "herlihyboy" <ryan.parmenter@.gmail.com> wrote in message
> news:1184946663.951045.202120@.g4g2000hsf.googlegroups.com...
>> I'm using Management Studio to manage and interface with our Sql
>> Server 2000 servers. One thing that doesn't (to me) appear to work as
>> well is the object scripting. I know they deprecated things from EM
>> 2000, that are now back in via SP2, but I'm not finding the useful
>> tools I previously enjoyed.
>> One thing I am having problems with is that the scripting behavior
>> performs AWFULLY. When I simply want to script an object (trigger,
>> table, etc.) to a new query editor window (like I previously could in
>> QA), it may take upwards of 30 seconds before what ends up being a
>> very short script is output to the query window. Why?
>> Unrelated to scripting, if my DB has a substantial number of stored
>> procs, it takes a LONG time for the list to appear. In EM / QA, it
>> would appear instantly.
>> Also, we used to be able to easily generate DROP statements at the top
>> of every script (table, trigger, proc, view, function, etc.). The only
>> way I see to do this, now, is to use the script wizard on the
>> database. If I want to quickly generate a single script, I don't want
>> to use a freaking wizard. Grrrr...
>> Finally, how can I get my table scripts to stop generating column-
>> level permissions? If an account has full access to a table, I really
>> don't care about the column-by-column access. I'm wondering if 'script
>> object level permissions' is adding to the LONG time it takes to
>> generate a script.
>> If anyone has tips on how to optimize the production of scripts,
>> please share. At this point, I kind of wish I had stayed with EM /
>> QA...
>> Ryan
>
Generating scripts in Management Studio
I'm using Management Studio to manage and interface with our Sql
Server 2000 servers. One thing that doesn't (to me) appear to work as
well is the object scripting. I know they deprecated things from EM
2000, that are now back in via SP2, but I'm not finding the useful
tools I previously enjoyed.
One thing I am having problems with is that the scripting behavior
performs AWFULLY. When I simply want to script an object (trigger,
table, etc.) to a new query editor window (like I previously could in
QA), it may take upwards of 30 seconds before what ends up being a
very short script is output to the query window. Why?
Unrelated to scripting, if my DB has a substantial number of stored
procs, it takes a LONG time for the list to appear. In EM / QA, it
would appear instantly.
Also, we used to be able to easily generate DROP statements at the top
of every script (table, trigger, proc, view, function, etc.). The only
way I see to do this, now, is to use the script wizard on the
database. If I want to quickly generate a single script, I don't want
to use a freaking wizard. Grrrr...
Finally, how can I get my table scripts to stop generating column-
level permissions? If an account has full access to a table, I really
don't care about the column-by-column access. I'm wondering if 'script
object level permissions' is adding to the LONG time it takes to
generate a script.
If anyone has tips on how to optimize the production of scripts,
please share. At this point, I kind of wish I had stayed with EM /
QA...
Ryan> One thing I am having problems with is that the scripting behavior
> performs AWFULLY. When I simply want to script an object (trigger,
> table, etc.) to a new query editor window (like I previously could in
> QA), it may take upwards of 30 seconds before what ends up being a
> very short script is output to the query window. Why?
Object scripting in general is a poor performer. But I have only really
noticed this when scripting a lot of objects. For single objects this seems
to be pretty quick. To be honest though, my habit for most code-based
objects is to right-click, modify rather than script as > ... less clicks.
When I need to generate scripts for many objects, I don't use EM or SSMS at
all... I use SQL Compare from red-gate (there are other similarly capable
tools, e.g. Apex SQL diff, and a free one from Quest). Just compare your
database against an empty database and you will get a set of objects you can
script or nor script using checkboxes.
It is extremely fast and the only issue I've ever had is trying to create an
indexed view and stored procedure referencing it in the wrong order. My
solution to not running into that issue has been to script views/tables
first, then procedures/functions. A lot of other options are there too,
such as temporarily disabling DDL triggers.
> Unrelated to scripting, if my DB has a substantial number of stored
> procs, it takes a LONG time for the list to appear. In EM / QA, it
> would appear instantly.
As a test, I have a server in our data center with 400+ databases. Each
database has 354 stored procedures. Over a VPN I can repeatedly pick any
random database and expand the stored procedure list in about a second. And
this is a production system (so it's not just a set of empty databases with
no activity).
If you're not talking about stored procedure counts that are higher by an
order of magnitude or more, this may be a machine resource issue. Perhaps
in EM/QA this list would have appeared even faster for me. :-)
Aaron Bertrand
SQL Server MVP|||Could your complaints be related to the INITIAL attempt to perform
something - in which case it is just all the frickin' .NET crap having to be
loaded/initialized.
TheSQLGuru
President
Indicium Resources, Inc.
"herlihyboy" <ryan.parmenter@.gmail.com> wrote in message
news:1184946663.951045.202120@.g4g2000hsf.googlegroups.com...
> I'm using Management Studio to manage and interface with our Sql
> Server 2000 servers. One thing that doesn't (to me) appear to work as
> well is the object scripting. I know they deprecated things from EM
> 2000, that are now back in via SP2, but I'm not finding the useful
> tools I previously enjoyed.
> One thing I am having problems with is that the scripting behavior
> performs AWFULLY. When I simply want to script an object (trigger,
> table, etc.) to a new query editor window (like I previously could in
> QA), it may take upwards of 30 seconds before what ends up being a
> very short script is output to the query window. Why?
> Unrelated to scripting, if my DB has a substantial number of stored
> procs, it takes a LONG time for the list to appear. In EM / QA, it
> would appear instantly.
> Also, we used to be able to easily generate DROP statements at the top
> of every script (table, trigger, proc, view, function, etc.). The only
> way I see to do this, now, is to use the script wizard on the
> database. If I want to quickly generate a single script, I don't want
> to use a freaking wizard. Grrrr...
> Finally, how can I get my table scripts to stop generating column-
> level permissions? If an account has full access to a table, I really
> don't care about the column-by-column access. I'm wondering if 'script
> object level permissions' is adding to the LONG time it takes to
> generate a script.
> If anyone has tips on how to optimize the production of scripts,
> please share. At this point, I kind of wish I had stayed with EM /
> QA...
> Ryan
>|||Probably not, I was scripting 300+ SPs and was watching the progress in the
script dialog to script them one by one, it was like molasses on a cold day.
Roman
Roman Rehak
http://sqlblog.com/blogs/roman_rehak
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:esXxMKvyHHA.2172@.TK2MSFTNGP06.phx.gbl...
> Could your complaints be related to the INITIAL attempt to perform
> something - in which case it is just all the frickin' .NET crap having to
> be loaded/initialized.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "herlihyboy" <ryan.parmenter@.gmail.com> wrote in message
> news:1184946663.951045.202120@.g4g2000hsf.googlegroups.com...
>
Server 2000 servers. One thing that doesn't (to me) appear to work as
well is the object scripting. I know they deprecated things from EM
2000, that are now back in via SP2, but I'm not finding the useful
tools I previously enjoyed.
One thing I am having problems with is that the scripting behavior
performs AWFULLY. When I simply want to script an object (trigger,
table, etc.) to a new query editor window (like I previously could in
QA), it may take upwards of 30 seconds before what ends up being a
very short script is output to the query window. Why?
Unrelated to scripting, if my DB has a substantial number of stored
procs, it takes a LONG time for the list to appear. In EM / QA, it
would appear instantly.
Also, we used to be able to easily generate DROP statements at the top
of every script (table, trigger, proc, view, function, etc.). The only
way I see to do this, now, is to use the script wizard on the
database. If I want to quickly generate a single script, I don't want
to use a freaking wizard. Grrrr...
Finally, how can I get my table scripts to stop generating column-
level permissions? If an account has full access to a table, I really
don't care about the column-by-column access. I'm wondering if 'script
object level permissions' is adding to the LONG time it takes to
generate a script.
If anyone has tips on how to optimize the production of scripts,
please share. At this point, I kind of wish I had stayed with EM /
QA...
Ryan> One thing I am having problems with is that the scripting behavior
> performs AWFULLY. When I simply want to script an object (trigger,
> table, etc.) to a new query editor window (like I previously could in
> QA), it may take upwards of 30 seconds before what ends up being a
> very short script is output to the query window. Why?
Object scripting in general is a poor performer. But I have only really
noticed this when scripting a lot of objects. For single objects this seems
to be pretty quick. To be honest though, my habit for most code-based
objects is to right-click, modify rather than script as > ... less clicks.
When I need to generate scripts for many objects, I don't use EM or SSMS at
all... I use SQL Compare from red-gate (there are other similarly capable
tools, e.g. Apex SQL diff, and a free one from Quest). Just compare your
database against an empty database and you will get a set of objects you can
script or nor script using checkboxes.
It is extremely fast and the only issue I've ever had is trying to create an
indexed view and stored procedure referencing it in the wrong order. My
solution to not running into that issue has been to script views/tables
first, then procedures/functions. A lot of other options are there too,
such as temporarily disabling DDL triggers.
> Unrelated to scripting, if my DB has a substantial number of stored
> procs, it takes a LONG time for the list to appear. In EM / QA, it
> would appear instantly.
As a test, I have a server in our data center with 400+ databases. Each
database has 354 stored procedures. Over a VPN I can repeatedly pick any
random database and expand the stored procedure list in about a second. And
this is a production system (so it's not just a set of empty databases with
no activity).
If you're not talking about stored procedure counts that are higher by an
order of magnitude or more, this may be a machine resource issue. Perhaps
in EM/QA this list would have appeared even faster for me. :-)
Aaron Bertrand
SQL Server MVP|||Could your complaints be related to the INITIAL attempt to perform
something - in which case it is just all the frickin' .NET crap having to be
loaded/initialized.
TheSQLGuru
President
Indicium Resources, Inc.
"herlihyboy" <ryan.parmenter@.gmail.com> wrote in message
news:1184946663.951045.202120@.g4g2000hsf.googlegroups.com...
> I'm using Management Studio to manage and interface with our Sql
> Server 2000 servers. One thing that doesn't (to me) appear to work as
> well is the object scripting. I know they deprecated things from EM
> 2000, that are now back in via SP2, but I'm not finding the useful
> tools I previously enjoyed.
> One thing I am having problems with is that the scripting behavior
> performs AWFULLY. When I simply want to script an object (trigger,
> table, etc.) to a new query editor window (like I previously could in
> QA), it may take upwards of 30 seconds before what ends up being a
> very short script is output to the query window. Why?
> Unrelated to scripting, if my DB has a substantial number of stored
> procs, it takes a LONG time for the list to appear. In EM / QA, it
> would appear instantly.
> Also, we used to be able to easily generate DROP statements at the top
> of every script (table, trigger, proc, view, function, etc.). The only
> way I see to do this, now, is to use the script wizard on the
> database. If I want to quickly generate a single script, I don't want
> to use a freaking wizard. Grrrr...
> Finally, how can I get my table scripts to stop generating column-
> level permissions? If an account has full access to a table, I really
> don't care about the column-by-column access. I'm wondering if 'script
> object level permissions' is adding to the LONG time it takes to
> generate a script.
> If anyone has tips on how to optimize the production of scripts,
> please share. At this point, I kind of wish I had stayed with EM /
> QA...
> Ryan
>|||Probably not, I was scripting 300+ SPs and was watching the progress in the
script dialog to script them one by one, it was like molasses on a cold day.
Roman
Roman Rehak
http://sqlblog.com/blogs/roman_rehak
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:esXxMKvyHHA.2172@.TK2MSFTNGP06.phx.gbl...
> Could your complaints be related to the INITIAL attempt to perform
> something - in which case it is just all the frickin' .NET crap having to
> be loaded/initialized.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "herlihyboy" <ryan.parmenter@.gmail.com> wrote in message
> news:1184946663.951045.202120@.g4g2000hsf.googlegroups.com...
>
Wednesday, March 21, 2012
Generating Scripts from SQL Server 2000 using DMO and C#
Hi,
I'm generate scripts from a sql server 2000 database using DMO and C#.
the probelm am having is that when i try to generate views i get views *and* tables!
i tried to get rid of the tables:
SQLDMO.Transfer2 transfer = new SQLDMO.Transfer2Class();
transfer.CopyAllViews = true;
transfer.CopyAllTables = false;
transfer.IncludeDependencies = false;
i tried adding the views using AddObject:
foreach (SQLDMO.DBObject o in db.ListObjects(SQLDMO.SQLDMO_OBJECT_TYPE.SQLDMOObj_View, SQLDMO.SQLDMO_OBJSORT_TYPE.SQLDMOObjSort_Name))
transfer.AddObject(o);
but still tables got generated!
what am i doing wrong? i know that this is doable coz i can do it using enterprise manager (it also uses DMO right?)
any help is greatly appreciated.
Thank you.
aghoneim
P.S.
Script generation options:
transfer.ScriptType = SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Default |
SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Drops |
SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_ToFileOnly;
transfer.Script2Type = SQLDMO.SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_UnicodeFile;
Is there a particular reason you want to use DMO? You could easily achieve what you want through SMO, which works fine against SQL 2000 You might be able to achieve what you want through DMO, but off hand I am not sure of code. If SMO won't work for you, I will try to dig up some DMO samples that "may" help you...Enterprise Manager does use DMO, but it may be doing some additional filtering on the client.
Peter
|||Hi Peter,
I have to use DMO coz we own SQL 2000, and also coz of build server restrictions (this is for a build script).
But assuming i got perm for installing SMO on the build server, can i get SMO without buying SQL 2005?
Thanks.
aghoneim
|||Hi,good news, yes you can.
http://www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&DisplayLang=en
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||
Hey,
Thanks alot :)
aghoneim
sqlGenerating Scripts for Table Alterations in SQLServer
Hi,
I am using SQLServer and I would like to track changes or generate scripts for every change or alteration made to the table.. Please help me with any ideas..
Thank You,
Lesego
Two ways are common.
1. Profiler. Kind of clunky, but more or less guaranteed to see every change made, and cannot be easily overridden
2. In 2005, use a DDL trigger/Event Notification. I use this all of the time to monitor changes to my databases by other people who have dbo rights in cases where things are too political to give more specific rights.
Generating Scripts at the database level
Hi All,
I require to compare two database structures.This is what I went for:
I generated scripts (right click on the database --> All Tasks--> Generate Scripts) for all tables in the two given databases and compared them.
The problem is that the order of the tables in the generated scripts are different. Can I control this order by any possible means?
Thanks in advance.
I do not know of a way offhand via the generate scripts wizard, however you can manually generate a script for each table, copy them to the clipboard, and then paste them into the desired order into a new query window.
hope this helps,
derek
|||You can 'cut and paste' the script files using your editor (Visual Notepad, for example).
Otherwise, you cannot 'control' the order of elements in the script that the scripting wizard creates.
Generating scripts and creating backups
Hi All,
When to opt for generating scripts and exporting data and when to create a
backup and restore the backup?
kd
If you are looking to maintain a standby server or a backup server, then
BACKUP/RESTORE is the safest way, as backup makes exact copies of the
database.
If you want to send your data to a non-SQL Server environment or database,
then you might want to export data out into text files. Also, even between 2
SQL Servers, if you just want to copy some tables (and not the entire
database), then you could go with DTS or BCP to export the data.
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"kd" <kd@.discussions.microsoft.com> wrote in message
news:4DC56605-A663-424F-98A4-147F1CE189AE@.microsoft.com...
> Hi All,
> When to opt for generating scripts and exporting data and when to create a
> backup and restore the backup?
> kd
|||you might want to geneerate scripts if your database is incorporated as part
of an automated build process and coupled to some software product.
Greg Jackson
PDX, Oregon
When to opt for generating scripts and exporting data and when to create a
backup and restore the backup?
kd
If you are looking to maintain a standby server or a backup server, then
BACKUP/RESTORE is the safest way, as backup makes exact copies of the
database.
If you want to send your data to a non-SQL Server environment or database,
then you might want to export data out into text files. Also, even between 2
SQL Servers, if you just want to copy some tables (and not the entire
database), then you could go with DTS or BCP to export the data.
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"kd" <kd@.discussions.microsoft.com> wrote in message
news:4DC56605-A663-424F-98A4-147F1CE189AE@.microsoft.com...
> Hi All,
> When to opt for generating scripts and exporting data and when to create a
> backup and restore the backup?
> kd
|||you might want to geneerate scripts if your database is incorporated as part
of an automated build process and coupled to some software product.
Greg Jackson
PDX, Oregon
Subscribe to:
Posts (Atom)