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/
>

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

Generating Model in Report Manager

Hi all,
I am trying to generate a model in report manager so i can use the
report builder function.
However when i click on 'Generate Model' then fill out a name and click
OK, it says: "You do not have sufficient permission to create this item
in this folder." However, i have administrator permissions and a
co-worker is unable to do it and he is the creator/manager of the
reporting services.
Any help/ideas are appreciated.
CheersAlso,
i tried the method of creating a Report Model Project in Sql Business
Intelligence Development Studio but again, when i tried to deploy it,
it gave me a permission error. And again, I do have administrators
access, so im not sure what kind of permissions i need.
Thanks|||I figured it out.
There was a problem with the type of permissions given.
I needed to go to site settings-Configure item-level role
definitions-Content Manager
then make sure everything is ticked off.
Then you should be good to go!!

generating database schema thru command line

In informix there is a tool called dbschema which can generate a sql file
to create a database fully. I am aware that in the Enterprise Manager one
can do it easily. However i need the same functionality via command line
so that we can plug the command in the build tool.
TIAEM does it using sql-dmo, take a look a this api.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_m_g_11rm.asp
AMB
"rkusenet" wrote:
> In informix there is a tool called dbschema which can generate a sql file
> to create a database fully. I am aware that in the Enterprise Manager one
> can do it easily. However i need the same functionality via command line
> so that we can plug the command in the build tool.
> TIA
>|||You can also invoke the functionality of the EM wizards using SQL-NS API.
Look for SQL-NS constant SQLNS_CmdID_GENERATE_SCRIPTS and SQL-NS method
ExecuteCommandByID.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlns/ns_ref_78z5.asp
AMB
"Alejandro Mesa" wrote:
> EM does it using sql-dmo, take a look a this api.
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_m_g_11rm.asp
>
> AMB
> "rkusenet" wrote:
> > In informix there is a tool called dbschema which can generate a sql file
> > to create a database fully. I am aware that in the Enterprise Manager one
> > can do it easily. However i need the same functionality via command line
> > so that we can plug the command in the build tool.
> >
> > TIA
> >|||I've listed some options here:
http://www.karaszi.com/SQLServer/info_generate_script.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"rkusenet" <usenet.rk@.gmail.com> wrote in message news:3d9nmvF6rhk35U1@.individual.net...
> In informix there is a tool called dbschema which can generate a sql file to create a database
> fully. I am aware that in the Enterprise Manager one can do it easily. However i need the same
> functionality via command line
> so that we can plug the command in the build tool.
> TIA|||Tibor,
Does SCPTXFR.EXE come with SQL Server 2000 also?
AMB
"rkusenet" wrote:
> In informix there is a tool called dbschema which can generate a sql file
> to create a database fully. I am aware that in the Enterprise Manager one
> can do it easily. However i need the same functionality via command line
> so that we can plug the command in the build tool.
> TIA
>|||you talked about doing a build, I'm wondering if this is part of your daily
or at least scheduled build process?
You may want to save yourself all the time and effort of re-inventing the
wheel...
http://www.dbghost.com
Build, compare, deploy.
"rkusenet" wrote:
> In informix there is a tool called dbschema which can generate a sql file
> to create a database fully. I am aware that in the Enterprise Manager one
> can do it easily. However i need the same functionality via command line
> so that we can plug the command in the build tool.
> TIA
>|||> Does SCPTXFR.EXE come with SQL Server 2000 also?
Seems so. In the upgrade directory :-).
C:\Program Files\Microsoft SQL Server\MSSQL\Upgrade
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:649EF740-EF67-4618-A363-B765823989DC@.microsoft.com...
> Tibor,
> Does SCPTXFR.EXE come with SQL Server 2000 also?
>
> AMB
> "rkusenet" wrote:
>> In informix there is a tool called dbschema which can generate a sql file
>> to create a database fully. I am aware that in the Enterprise Manager one
>> can do it easily. However i need the same functionality via command line
>> so that we can plug the command in the build tool.
>> TIA|||Thanks!!!
"Tibor Karaszi" wrote:
> > Does SCPTXFR.EXE come with SQL Server 2000 also?
> Seems so. In the upgrade directory :-).
> C:\Program Files\Microsoft SQL Server\MSSQL\Upgrade
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:649EF740-EF67-4618-A363-B765823989DC@.microsoft.com...
> > Tibor,
> >
> > Does SCPTXFR.EXE come with SQL Server 2000 also?
> >
> >
> > AMB
> >
> > "rkusenet" wrote:
> >
> >> In informix there is a tool called dbschema which can generate a sql file
> >> to create a database fully. I am aware that in the Enterprise Manager one
> >> can do it easily. However i need the same functionality via command line
> >> so that we can plug the command in the build tool.
> >>
> >> TIA
> >>
>
>

Generate SQL Scripts

Hi all,
In Enterprise Manager, I could right click on a database and "generate
SQL Scripts" for hundreds of objects at once. In 2005, though, I've
only been able to generate scripts one at a time in Object Explorer,
and I haven't found any way to change the scripting option. Surely I'm
missing something?
- How do I generate scripts in 2005 (for many objects at a time, with
some custom options, as in SQL2000)?
- How do I change the Object Explorer right-click scripting options in
2005, when I'm scripting single objects?
Thanks in advance,
Myron> - How do I generate scripts in 2005 (for many objects at a time, with
> some custom options, as in SQL2000)?
Yes, seems like a step backward in this regard. Here are some excerpts from
a thread in MSDN's tools_general group that revolved around scripting all
the stored procedures (which could of course be replicated for several other
types of objects as well):
<I posted this>
Well, knowing that we can do this:
SELECT Object_Definition(Object_ID) FROM sys.procedures
The following might seem like a real brute force approach, but this will
generate the command line scripts necessary to do this (don't forget to use
the correct server and database, and change -E to -U/-P if using SQL auth):
select 'osql -S -E server -d database -Q"SELECT Object_Definition(Object_ID)
from sys.procedures
WHERE name='''+name+'''" > c:'+name+'.PRC' FROM sys.procedures
Take the output, paste into a .bat file, and run. (You may wish to modify
the result first, e.g. leave out specific procedures.) This should make the
generation of the files pretty painless, though they will not include any of
the typical SET options ON at the top and OFF at the end.
Of course, you could take this further, like writing these commands directly
to a batch file by executing xp_cmdshell directly, and then even executing
the file when you're done. But I was too lazy to deal with the nesting
quotes, which would quickly get far beyond anything I'd have the patience
for right now. :-) I also didn't want to get into the process of enabling
xp_cmdshell, which is disabled by default, and for good reason. If you have
enabled xp_cmdshell, feel free to experiment.
<Nilton posted this>
Hi,
I do not test in SQL Server 2005 but SCPTXFR.EXE is a very good command
line utility for it. It can be found in the C:\Program Files\Microsoft
SQL Server\MSSQL\Upgrade (SQL Server 2000) or x86\upgrade in SQL Server
2000 CD. It appears to be missing in SQL Server 2005 but...
More info..
http://www.norbtechnologies.com/pdf...>
%20Script.pdf|||Actually, in Microsoft SQL Server Management Studio, if you open your
database in Object Explorer, then go to tasks --> generate scripts and follo
w
the wizard through each step, you can script out any part of your database.
I
use this very often and it works very well.
HTH, Todd
"Aaron Bertrand [SQL Server MVP]" wrote:

> Yes, seems like a step backward in this regard. Here are some excerpts fr
om
> a thread in MSDN's tools_general group that revolved around scripting all
> the stored procedures (which could of course be replicated for several oth
er
> types of objects as well):
> <I posted this>
> Well, knowing that we can do this:
> SELECT Object_Definition(Object_ID) FROM sys.procedures
> The following might seem like a real brute force approach, but this will
> generate the command line scripts necessary to do this (don't forget to us
e
> the correct server and database, and change -E to -U/-P if using SQL auth)
:
> select 'osql -S -E server -d database -Q"SELECT Object_Definition(Object_I
D)
> from sys.procedures
> WHERE name='''+name+'''" > c:'+name+'.PRC' FROM sys.procedures
> Take the output, paste into a .bat file, and run. (You may wish to modify
> the result first, e.g. leave out specific procedures.) This should make t
he
> generation of the files pretty painless, though they will not include any
of
> the typical SET options ON at the top and OFF at the end.
> Of course, you could take this further, like writing these commands direct
ly
> to a batch file by executing xp_cmdshell directly, and then even executing
> the file when you're done. But I was too lazy to deal with the nesting
> quotes, which would quickly get far beyond anything I'd have the patience
> for right now. :-) I also didn't want to get into the process of enablin
g
> xp_cmdshell, which is disabled by default, and for good reason. If you ha
ve
> enabled xp_cmdshell, feel free to experiment.
>
> <Nilton posted this>
> Hi,
> I do not test in SQL Server 2005 but SCPTXFR.EXE is a very good command
> line utility for it. It can be found in the C:\Program Files\Microsoft
> SQL Server\MSSQL\Upgrade (SQL Server 2000) or x86\upgrade in SQL Server
> 2000 CD. It appears to be missing in SQL Server 2005 but...
> More info..
> http://www.norbtechnologies.com/pdf...on%20Script.pdf
>
>|||Another alternative is selecting a group of elements in the Summary window
and selecting Script > ... from the context menu. E.g. when the Tables node
is selected in the Object Explorer, the Summary window contains the list of
all user tables - select one or more to create scripts.
ML|||Thanks, all.
Anyone know how to get the scripting to add the DROP command or
permissions, like it did in 2000?|||Thanks, all.
Anyone know how to get the scripting to add the DROP command or
permissions, like it did in 2000?|||It's truly a step backwards. I'm writing code to do this now. It's only
about 30% complete but I'm getting tables, sps, views, triggers and
functions. Working on jobs right now. If you're interested I'll email you
the current code, unless you'd prefer to wait until its 100%.
It's a DLL and a front end console application with the following usage.
I'll add a GUI when I have time:
========================================
========================
DatabaseScriptor [-Out="{output folder}"][ -Preserve]
[ -Server="{name}"][ -Database="{name}"][ -User="" -Password=""]
[ -Include {objects}][ -Exclude {objects}][ -Encrypt][ -?]
[ -Force][ -NoDirs][-Quiet]
========================================
========================
This application is used to script objects in a named SQL Server database to
flat files.
SQL Server 2005's scripting functionality removed the ability to do this as
SQL Server 2000 did from the user interface.
========================================
========================
-? = Show this help text.
-D[atabase] = Optional. The name of the database whose object(s) will be
scripted. If not specified the user/login's default database
will be used.
-En[crypt] = Optional. If encryption is available, force encrypted
communication.
-Ex[clude] = Optional. The list of objects to exclude. If not specified no
objects will be exclueded.
-F[orce] = Optional. If true the output files will be overwritten
regardless of readonly state. Default is False.
-I[nclude] = Optional. The list of objects to script. If not specified all
objects will be included.
-N[oDirs] = Optional. If specified all files will be output to the specified
folder. Specialized folders will not be created.
-O[ut] = Optional. The folder to which to build the object tree. If not
specified all objects will be scripted to the console.
Duplicate file names will be overwritten.
-Pa[ssword] = Optional. The password for the SQL Server login.
-Q[uiet] = Optional. If specified no output will be sent to the console.
By default object names will be shown.
-Pr[eserve] = Optional. If true the output files will NOT be overwritten
regardless of readonly state. If false all files are overwritten.
Default is False.
-S[erver] = Optional. The name of the server that contains the database.
If not set, (local) will be assumed.
-U[ser] = Optional. The SQL Server login name to use for the scripting
operation. If not supplied the active user's account will be
used.
========================================
========================

Generate SQL Script Error

Hi,
I'm getting an error when I attempt to generate a SQL script from Enterprise
Manager:
[SQL-DMO]CreateFile error on 'servername.dbname.DP1'.
I googled it and found one receommendation to uninstall McAfee. I did so
and it made no difference.
I found another suggestion to save it to a fully-qualified UNC path (not
just clicking on "My Docments"), but that made no difference.
Does anyone know what could be causing this error?
It doesn't matter if I script all objects or just one type of object. It
doesn't matter if I save it to a file or just try to "Preview" it -- either
way I get the same error message.
Thanks for any help that can be provided.
ChuckForgot to include the essentials:
SQL Server 2000 sp3a running on Windows 2000 Advanced Server (fully
patched).
"CR" <chuck.rich__ardson@.sfcc.edu> wrote in message
news:u%238$rOswFHA.2880@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm getting an error when I attempt to generate a SQL script from
Enterprise
> Manager:
> [SQL-DMO]CreateFile error on 'servername.dbname.DP1'.
> I googled it and found one receommendation to uninstall McAfee. I did so
> and it made no difference.
> I found another suggestion to save it to a fully-qualified UNC path (not
> just clicking on "My Docments"), but that made no difference.
> Does anyone know what could be causing this error?
> It doesn't matter if I script all objects or just one type of object. It
> doesn't matter if I save it to a file or just try to "Preview" it --
either
> way I get the same error message.
> Thanks for any help that can be provided.
> Chuck
>

Generate SQL Script

Hi,
To generate SQL script, we can right click the database in SQL Server
Enterprise Manager. But is there any way that I can schedule to generate
this script automatically?
Thank you for your help, and any feedback will highly appreciated.
Billy
Yes. You can hijack the sample DMO code that comes with SQL and alter it to
script out your objects to a text file using a win32 DTS task scheduled in a
job..
"Billy Leung" wrote:

> Hi,
> To generate SQL script, we can right click the database in SQL Server
> Enterprise Manager. But is there any way that I can schedule to generate
> this script automatically?
> Thank you for your help, and any feedback will highly appreciated.
>
> Billy
>
>

Generate SQL Script

Hi,
To generate SQL script, we can right click the database in SQL Server
Enterprise Manager. But is there any way that I can schedule to generate
this script automatically?
Thank you for your help, and any feedback will highly appreciated.
BillyYes. You can hijack the sample DMO code that comes with SQL and alter it to
script out your objects to a text file using a win32 DTS task scheduled in a
job..
"Billy Leung" wrote:
> Hi,
> To generate SQL script, we can right click the database in SQL Server
> Enterprise Manager. But is there any way that I can schedule to generate
> this script automatically?
> Thank you for your help, and any feedback will highly appreciated.
>
> Billy
>
>

Generate SQL Script

Hi,
To generate SQL script, we can right click the database in SQL Server
Enterprise Manager. But is there any way that I can schedule to generate
this script automatically?
Thank you for your help, and any feedback will highly appreciated.
BillyYes. You can hijack the sample DMO code that comes with SQL and alter it to
script out your objects to a text file using a win32 DTS task scheduled in a
job..
"Billy Leung" wrote:

> Hi,
> To generate SQL script, we can right click the database in SQL Server
> Enterprise Manager. But is there any way that I can schedule to generate
> this script automatically?
> Thank you for your help, and any feedback will highly appreciated.
>
> Billy
>
>

Generate SQL insert statement fro SQL Server database

Good day,

I have seen in Enterprise manager there is a toll that can script the all tables in a database, but nothing that can generate the insert statements for all the rows in each table in a secified database.

Does any one know of a application, plug in, script that can generate the insert statments for all the tables in a database?

Please someone help, this is driving me insane.

ThanksFound the answer, great sacript.

SQL Insert generator script - http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=1052&lngWId=5|||I wrote this a while back for my own use
Call this sp in a loop over SELECT TABLE_NAME FROM INFORMATION_SCHEMA.tables

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE spGenerateInsertStatement
(
@.TableName varchar(255),
@.SQL_INSERT varchar(8000) OUTPUT
)
AS

DECLARE @.current_column varchar(255)
DECLARE @.current_ordinal int
DECLARE @.max_ordinal int

--pre assign some values for safety
SET @.current_column = ''
SET @.current_ordinal = 0
SET @.max_ordinal = 0

SET @.SQL_INSERT = 'INSERT INTO [' + @.TableName + '] ('

--get the max ordinal for the table you're inserting into
SELECT @.max_ordinal = MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.columns
WHERE TABLE_NAME = @.TableName

--create a cursor of column names and ordinals
DECLARE cInsert CURSOR
FOR
SELECT COLUMN_NAME,
ORDINAL_POSITION
FROM INFORMATION_SCHEMA.columns
WHERE TABLE_NAME = @.TableName
ORDER BY ORDINAL_POSITION

OPEN cInsert

--fetch the next record
FETCH NEXT FROM cInsert INTO @.current_column,@.current_ordinal

WHILE(@.@.FETCH_STATUS = 0)
BEGIN


IF(@.current_ordinal != @.max_ordinal)
BEGIN
--append "column," to the insert statement
SET @.SQL_INSERT = @.SQL_INSERT + '[' +@.current_column + '],' + CHAR(13) + CHAR(10)
END
ELSE
BEGIN
--append "column)" to the insert statement
SET @.SQL_INSERT = @.SQL_INSERT + '[' + @.current_column + '])' + CHAR(13) + CHAR(10)
END

--fetch the next record
FETCH NEXT FROM cInsert INTO @.current_column,@.current_ordinal
END

--cleanup cursor
CLOSE cInsert
DEALLOCATE cInsert

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO|||Hi! I'm on your side now!|||Hi! I just joined the forum to learn more about sql server 2000 and make friends too!|||Raski, what do you mean you are on our side? Where you from?

Generate Script without Ent. Manager

Hi,
I'm looking for script sample allowing me to do the same things than
"Generate Script" from Enterprise Manager.
I want to be able to generate the scripts from e.g, a SP.
I'm using SQL2000
Thanks,
Chriis> I'm looking for script sample allowing me to do the same things than
> "Generate Script" from Enterprise Manager.
> I want to be able to generate the scripts from e.g, a SP.
Here is a nice article:
http://www.databasejournal.com/features/mssql/article.php/2205291.
Still, I prefer using VBScript instead of sp_OA* procedures.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Thanks !
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:%23Wo%23CAJ0EHA.2804@.TK2MSFTNGP15.phx.gbl...
> > I'm looking for script sample allowing me to do the same things than
> > "Generate Script" from Enterprise Manager.
> > I want to be able to generate the scripts from e.g, a SP.
> Here is a nice article:
> http://www.databasejournal.com/features/mssql/article.php/2205291.
> Still, I prefer using VBScript instead of sp_OA* procedures.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>

Generate Script without Ent. Manager

Hi,
I'm looking for script sample allowing me to do the same things than
"Generate Script" from Enterprise Manager.
I want to be able to generate the scripts from e.g, a SP.
I'm using SQL2000
Thanks,
Chriis> I'm looking for script sample allowing me to do the same things than
> "Generate Script" from Enterprise Manager.
> I want to be able to generate the scripts from e.g, a SP.
Here is a nice article:
http://www.databasejournal.com/feat...le.php/2205291.
Still, I prefer using VBScript instead of sp_OA* procedures.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Thanks !
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:%23Wo%23CAJ0EHA.2804@.TK2MSFTNGP15.phx.gbl...
> Here is a nice article:
> http://www.databasejournal.com/feat...le.php/2205291.
> Still, I prefer using VBScript instead of sp_OA* procedures.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>

Generate Script without Ent. Manager

Hi,
I'm looking for script sample allowing me to do the same things than
"Generate Script" from Enterprise Manager.
I want to be able to generate the scripts from e.g, a SP.
I'm using SQL2000
Thanks,
Chriis
> I'm looking for script sample allowing me to do the same things than
> "Generate Script" from Enterprise Manager.
> I want to be able to generate the scripts from e.g, a SP.
Here is a nice article:
http://www.databasejournal.com/featu...e.php/2205291.
Still, I prefer using VBScript instead of sp_OA* procedures.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Thanks !
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:%23Wo%23CAJ0EHA.2804@.TK2MSFTNGP15.phx.gbl...
> Here is a nice article:
> http://www.databasejournal.com/featu...e.php/2205291.
> Still, I prefer using VBScript instead of sp_OA* procedures.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>