Script to alter Identity, Identity Seed, Identity Increment

Hi all
I need some Help in creating a SQL Script which I can Run direct on MS SQL
2000 Server.
Of existing tables I want to change the:
- Identity
- Identity Seed
- Identity Increment
I already tried something with ALTER TABLE, but I guess I did something
wrong, did not work sofar.
Would be glad if someone could give me an example of how to do this.
Thanks for the help
Cheers
MarcelMarcel
1) What do you mean "change indentity"? You can drop the column defined as
an IDENTITY property
2)DBCC CHECKIDENT
3) DROP column and re-create with a new Increment
"Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
news:%23ceE0KeSHHA.4844@.TK2MSFTNGP03.phx.gbl...
> Hi all
> I need some Help in creating a SQL Script which I can Run direct on MS SQL
> 2000 Server.
> Of existing tables I want to change the:
> - Identity
> - Identity Seed
> - Identity Increment
> I already tried something with ALTER TABLE, but I guess I did something
> wrong, did not work sofar.
> Would be glad if someone could give me an example of how to do this.
> Thanks for the help
> Cheers
> Marcel
>|||On Feb 6, 1:33 pm, "Marcel Stoop" <marcel.st...@.synspace.com> wrote:
> Hi all
> I need some Help in creating a SQL Script which I can Run direct on MS SQL
> 2000 Server.
>
You can change the identity seed with dbcc checkident (you can see
more details in BOL). I don't know of any supported way to modify
the identity increment.
Adi

> Of existing tables I want to change the:
> - Identity
> - Identity Seed
> - Identity Increment
> I already tried something with ALTER TABLE, but I guess I did something
> wrong, did not work sofar.
> Would be glad if someone could give me an example of how to do this.
> Thanks for the help
> Cheers
> Marcel|||With change I mean:
For Table X, the Collumn Name with the Primary Key, has Identity set to No
For this Collumn I want to Set the Identity to:Yes, with Identity Seed = 0
and Identity Increment = 1.
Because I have to do this more then once for several Tables, I do not want
to do this manually but through a Script.
The thing is: do not have a clue how to do it.
Cheers
Marcel
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:ebvO2SeSHHA.4260@.TK2MSFTNGP06.phx.gbl...
> Marcel
> 1) What do you mean "change indentity"? You can drop the column defined as
> an IDENTITY property
> 2)DBCC CHECKIDENT
> 3) DROP column and re-create with a new Increment
>
>
> "Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
> news:%23ceE0KeSHHA.4844@.TK2MSFTNGP03.phx.gbl...
>|||Marcel
> With change I mean:
> For Table X, the Collumn Name with the Primary Key, has Identity set to No
> For this Collumn I want to Set the Identity to:Yes, with Identity Seed = 0
> and Identity Increment = 1.
You cannot do that
CREATE TABLE Test (c INT NOT NULL)
INSERT INTO Test VALUES (1)
INSERT INTO Test VALUES (2)
--Want to add an IDENTITY Property
ALTER TABLE Test ADD c1 INT NOT NULL IDENTITY(1,1)
GO
ALTER TABLE Test DROP COLUMN c
GO
sp_rename 'Test.c1','c','column'
GO
SELECT * FROM Test
DROP TABLE Test
"Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
news:OI0qPKfSHHA.4956@.TK2MSFTNGP04.phx.gbl...
> With change I mean:
> For Table X, the Collumn Name with the Primary Key, has Identity set to No
> For this Collumn I want to Set the Identity to:Yes, with Identity Seed = 0
> and Identity Increment = 1.
> Because I have to do this more then once for several Tables, I do not want
> to do this manually but through a Script.
> The thing is: do not have a clue how to do it.
> Cheers
> Marcel
>
> "Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
> news:ebvO2SeSHHA.4260@.TK2MSFTNGP06.phx.gbl...
>|||Thanks for the Answer
I will try that with ALTER TABLE
Cheers
Marcel
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:eQO0vRfSHHA.2212@.TK2MSFTNGP02.phx.gbl...
> Marcel
> You cannot do that
> CREATE TABLE Test (c INT NOT NULL)
> INSERT INTO Test VALUES (1)
> INSERT INTO Test VALUES (2)
> --Want to add an IDENTITY Property
> ALTER TABLE Test ADD c1 INT NOT NULL IDENTITY(1,1)
> GO
> ALTER TABLE Test DROP COLUMN c
> GO
> sp_rename 'Test.c1','c','column'
> GO
> SELECT * FROM Test
> DROP TABLE Test
>
>
> "Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
> news:OI0qPKfSHHA.4956@.TK2MSFTNGP04.phx.gbl...
>

Script Syntax Errors

i have new server, win2003, sql2005. trying to restore a .bak file from
sql2000. when excuting the RESTORE DATABASE dbase1 from
disk='c:\dbasebackup.bak' command, or using FILELISTONLY, the error below is
displayed.
Executing the query ...
Query (1, 9) Parser: The syntax for 'database' is incorrect.
Execution complete
any ideas?
thanks in advance.
paulWhere do you execute this restore command? The error seems like some client application error...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"paul" <paul@.discussions.microsoft.com> wrote in message
news:2DBA2DA0-FEE5-481A-AF28-E1D9558BFE05@.microsoft.com...
>i have new server, win2003, sql2005. trying to restore a .bak file from
> sql2000. when excuting the RESTORE DATABASE dbase1 from
> disk='c:\dbasebackup.bak' command, or using FILELISTONLY, the error below is
> displayed.
> Executing the query ...
> Query (1, 9) Parser: The syntax for 'database' is incorrect.
> Execution complete
> any ideas?
> thanks in advance.
> paul|||i'm in the server management studio, right click on the database, new query,
mdx.
thanks.
paul
"Tibor Karaszi" wrote:
> Where do you execute this restore command? The error seems like some client application error...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "paul" <paul@.discussions.microsoft.com> wrote in message
> news:2DBA2DA0-FEE5-481A-AF28-E1D9558BFE05@.microsoft.com...
> >i have new server, win2003, sql2005. trying to restore a .bak file from
> > sql2000. when excuting the RESTORE DATABASE dbase1 from
> > disk='c:\dbasebackup.bak' command, or using FILELISTONLY, the error below is
> > displayed.
> >
> > Executing the query ...
> > Query (1, 9) Parser: The syntax for 'database' is incorrect.
> > Execution complete
> >
> > any ideas?
> > thanks in advance.
> > paul
>|||mdx? Seems you have connected to Analysis server. The restore command you posted is a SQL Server
command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"paul" <paul@.discussions.microsoft.com> wrote in message
news:13EB6139-0A6A-4F67-9F6F-12B430BE9DC3@.microsoft.com...
> i'm in the server management studio, right click on the database, new query,
> mdx.
> thanks.
> paul
> "Tibor Karaszi" wrote:
>> Where do you execute this restore command? The error seems like some client application error...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "paul" <paul@.discussions.microsoft.com> wrote in message
>> news:2DBA2DA0-FEE5-481A-AF28-E1D9558BFE05@.microsoft.com...
>> >i have new server, win2003, sql2005. trying to restore a .bak file from
>> > sql2000. when excuting the RESTORE DATABASE dbase1 from
>> > disk='c:\dbasebackup.bak' command, or using FILELISTONLY, the error below is
>> > displayed.
>> >
>> > Executing the query ...
>> > Query (1, 9) Parser: The syntax for 'database' is incorrect.
>> > Execution complete
>> >
>> > any ideas?
>> > thanks in advance.
>> > paul
>>|||oops, you are correct.
thanks.
when i try to connect to Database Engines a connection error is displayed;
name pipes provider error 40 and ms sql server error 53
"Tibor Karaszi" wrote:
> mdx? Seems you have connected to Analysis server. The restore command you posted is a SQL Server
> command.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "paul" <paul@.discussions.microsoft.com> wrote in message
> news:13EB6139-0A6A-4F67-9F6F-12B430BE9DC3@.microsoft.com...
> > i'm in the server management studio, right click on the database, new query,
> > mdx.
> > thanks.
> > paul
> >
> > "Tibor Karaszi" wrote:
> >
> >> Where do you execute this restore command? The error seems like some client application error...
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "paul" <paul@.discussions.microsoft.com> wrote in message
> >> news:2DBA2DA0-FEE5-481A-AF28-E1D9558BFE05@.microsoft.com...
> >> >i have new server, win2003, sql2005. trying to restore a .bak file from
> >> > sql2000. when excuting the RESTORE DATABASE dbase1 from
> >> > disk='c:\dbasebackup.bak' command, or using FILELISTONLY, the error below is
> >> > displayed.
> >> >
> >> > Executing the query ...
> >> > Query (1, 9) Parser: The syntax for 'database' is incorrect.
> >> > Execution complete
> >> >
> >> > any ideas?
> >> > thanks in advance.
> >> > paul
> >>
> >>
>|||Make sure the server is listening on appropriate netlibs (SQL Server Configuration Manager).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"paul" <paul@.discussions.microsoft.com> wrote in message
news:7FB8AAE2-17F5-4BCB-9E53-CBF44547A053@.microsoft.com...
> oops, you are correct.
> thanks.
> when i try to connect to Database Engines a connection error is displayed;
> name pipes provider error 40 and ms sql server error 53
>
> "Tibor Karaszi" wrote:
>> mdx? Seems you have connected to Analysis server. The restore command you posted is a SQL Server
>> command.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "paul" <paul@.discussions.microsoft.com> wrote in message
>> news:13EB6139-0A6A-4F67-9F6F-12B430BE9DC3@.microsoft.com...
>> > i'm in the server management studio, right click on the database, new query,
>> > mdx.
>> > thanks.
>> > paul
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Where do you execute this restore command? The error seems like some client application
>> >> error...
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >> "paul" <paul@.discussions.microsoft.com> wrote in message
>> >> news:2DBA2DA0-FEE5-481A-AF28-E1D9558BFE05@.microsoft.com...
>> >> >i have new server, win2003, sql2005. trying to restore a .bak file from
>> >> > sql2000. when excuting the RESTORE DATABASE dbase1 from
>> >> > disk='c:\dbasebackup.bak' command, or using FILELISTONLY, the error below is
>> >> > displayed.
>> >> >
>> >> > Executing the query ...
>> >> > Query (1, 9) Parser: The syntax for 'database' is incorrect.
>> >> > Execution complete
>> >> >
>> >> > any ideas?
>> >> > thanks in advance.
>> >> > paul
>> >>
>> >>
>>|||in SQL Server Config Manager there are 3 items listed; Service, Network
Config, Client Config.
in Services all are running; Integration,Analysis,Reporting, and Browser
in Network Config there are no items listed
in Client config, Protocols, all are running; Shared, NamePipes, TCP/IP, and
VIA
thanks.
paul
"Tibor Karaszi" wrote:
> Make sure the server is listening on appropriate netlibs (SQL Server Configuration Manager).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "paul" <paul@.discussions.microsoft.com> wrote in message
> news:7FB8AAE2-17F5-4BCB-9E53-CBF44547A053@.microsoft.com...
> > oops, you are correct.
> > thanks.
> >
> > when i try to connect to Database Engines a connection error is displayed;
> > name pipes provider error 40 and ms sql server error 53
> >
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> mdx? Seems you have connected to Analysis server. The restore command you posted is a SQL Server
> >> command.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "paul" <paul@.discussions.microsoft.com> wrote in message
> >> news:13EB6139-0A6A-4F67-9F6F-12B430BE9DC3@.microsoft.com...
> >> > i'm in the server management studio, right click on the database, new query,
> >> > mdx.
> >> > thanks.
> >> > paul
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> Where do you execute this restore command? The error seems like some client application
> >> >> error...
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >>
> >> >>
> >> >> "paul" <paul@.discussions.microsoft.com> wrote in message
> >> >> news:2DBA2DA0-FEE5-481A-AF28-E1D9558BFE05@.microsoft.com...
> >> >> >i have new server, win2003, sql2005. trying to restore a .bak file from
> >> >> > sql2000. when excuting the RESTORE DATABASE dbase1 from
> >> >> > disk='c:\dbasebackup.bak' command, or using FILELISTONLY, the error below is
> >> >> > displayed.
> >> >> >
> >> >> > Executing the query ...
> >> >> > Query (1, 9) Parser: The syntax for 'database' is incorrect.
> >> >> > Execution complete
> >> >> >
> >> >> > any ideas?
> >> >> > thanks in advance.
> >> >> > paul
> >> >>
> >> >>
> >>
> >>
>|||Seems you didn't install SQL Server:
> in Services all are running; Integration,Analysis,Reporting, and Browser
If you had installed SQL Server, you would have also a "SQL Server" service.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"paul" <paul@.discussions.microsoft.com> wrote in message
news:BFEF1B78-052E-4EB1-8A9A-D3804256436C@.microsoft.com...
> in SQL Server Config Manager there are 3 items listed; Service, Network
> Config, Client Config.
> in Services all are running; Integration,Analysis,Reporting, and Browser
> in Network Config there are no items listed
> in Client config, Protocols, all are running; Shared, NamePipes, TCP/IP, and
> VIA
> thanks.
> paul
> "Tibor Karaszi" wrote:
>> Make sure the server is listening on appropriate netlibs (SQL Server Configuration Manager).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "paul" <paul@.discussions.microsoft.com> wrote in message
>> news:7FB8AAE2-17F5-4BCB-9E53-CBF44547A053@.microsoft.com...
>> > oops, you are correct.
>> > thanks.
>> >
>> > when i try to connect to Database Engines a connection error is displayed;
>> > name pipes provider error 40 and ms sql server error 53
>> >
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> mdx? Seems you have connected to Analysis server. The restore command you posted is a SQL
>> >> Server
>> >> command.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >> "paul" <paul@.discussions.microsoft.com> wrote in message
>> >> news:13EB6139-0A6A-4F67-9F6F-12B430BE9DC3@.microsoft.com...
>> >> > i'm in the server management studio, right click on the database, new query,
>> >> > mdx.
>> >> > thanks.
>> >> > paul
>> >> >
>> >> > "Tibor Karaszi" wrote:
>> >> >
>> >> >> Where do you execute this restore command? The error seems like some client application
>> >> >> error...
>> >> >>
>> >> >> --
>> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> http://sqlblog.com/blogs/tibor_karaszi
>> >> >>
>> >> >>
>> >> >> "paul" <paul@.discussions.microsoft.com> wrote in message
>> >> >> news:2DBA2DA0-FEE5-481A-AF28-E1D9558BFE05@.microsoft.com...
>> >> >> >i have new server, win2003, sql2005. trying to restore a .bak file from
>> >> >> > sql2000. when excuting the RESTORE DATABASE dbase1 from
>> >> >> > disk='c:\dbasebackup.bak' command, or using FILELISTONLY, the error below is
>> >> >> > displayed.
>> >> >> >
>> >> >> > Executing the query ...
>> >> >> > Query (1, 9) Parser: The syntax for 'database' is incorrect.
>> >> >> > Execution complete
>> >> >> >
>> >> >> > any ideas?
>> >> >> > thanks in advance.
>> >> >> > paul
>> >> >>
>> >> >>
>> >>
>> >>
>>|||i'm an idiot.
thanks.
paul
"Tibor Karaszi" wrote:
> Seems you didn't install SQL Server:
> > in Services all are running; Integration,Analysis,Reporting, and Browser
> If you had installed SQL Server, you would have also a "SQL Server" service.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "paul" <paul@.discussions.microsoft.com> wrote in message
> news:BFEF1B78-052E-4EB1-8A9A-D3804256436C@.microsoft.com...
> > in SQL Server Config Manager there are 3 items listed; Service, Network
> > Config, Client Config.
> >
> > in Services all are running; Integration,Analysis,Reporting, and Browser
> > in Network Config there are no items listed
> > in Client config, Protocols, all are running; Shared, NamePipes, TCP/IP, and
> > VIA
> >
> > thanks.
> > paul
> >
> > "Tibor Karaszi" wrote:
> >
> >> Make sure the server is listening on appropriate netlibs (SQL Server Configuration Manager).
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "paul" <paul@.discussions.microsoft.com> wrote in message
> >> news:7FB8AAE2-17F5-4BCB-9E53-CBF44547A053@.microsoft.com...
> >> > oops, you are correct.
> >> > thanks.
> >> >
> >> > when i try to connect to Database Engines a connection error is displayed;
> >> > name pipes provider error 40 and ms sql server error 53
> >> >
> >> >
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> mdx? Seems you have connected to Analysis server. The restore command you posted is a SQL
> >> >> Server
> >> >> command.
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >>
> >> >>
> >> >> "paul" <paul@.discussions.microsoft.com> wrote in message
> >> >> news:13EB6139-0A6A-4F67-9F6F-12B430BE9DC3@.microsoft.com...
> >> >> > i'm in the server management studio, right click on the database, new query,
> >> >> > mdx.
> >> >> > thanks.
> >> >> > paul
> >> >> >
> >> >> > "Tibor Karaszi" wrote:
> >> >> >
> >> >> >> Where do you execute this restore command? The error seems like some client application
> >> >> >> error...
> >> >> >>
> >> >> >> --
> >> >> >> Tibor Karaszi, SQL Server MVP
> >> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >> >>
> >> >> >>
> >> >> >> "paul" <paul@.discussions.microsoft.com> wrote in message
> >> >> >> news:2DBA2DA0-FEE5-481A-AF28-E1D9558BFE05@.microsoft.com...
> >> >> >> >i have new server, win2003, sql2005. trying to restore a .bak file from
> >> >> >> > sql2000. when excuting the RESTORE DATABASE dbase1 from
> >> >> >> > disk='c:\dbasebackup.bak' command, or using FILELISTONLY, the error below is
> >> >> >> > displayed.
> >> >> >> >
> >> >> >> > Executing the query ...
> >> >> >> > Query (1, 9) Parser: The syntax for 'database' is incorrect.
> >> >> >> > Execution complete
> >> >> >> >
> >> >> >> > any ideas?
> >> >> >> > thanks in advance.
> >> >> >> > paul
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >>
> >>
>

script objects into separate files in SQL 05

Hi,
In SQL2000 there used to be an option to script selected database objects
into separate files - cannot find this option in 2005 version.
I used to use it to script all database objects and put them under VSS for
easier management.
Could anyone please tell where this option could be found, or what is the
preferred way to script database objects into files (I have a database with
about a hundred tables and a few hundred derived objects - views, functions,
procs, etc.) I do not see it practical to create one file per object type.
Any help is greatly appreciated.
Hi
http://www.nigelrivett.net/DMO/AddToSourceSafe.html
Add Reference to Microosft.SQLServer.ConnectionInfo
Add Reference to Microosft.SQLServer.SMO
Imports System.IO
Imports Microsoft.SqlServer.Management.Smo
...
Dim SMOServer As Server = New Server("WARDYIT01")
SMOServer.SetDefaultInitFields(GetType(StoredProce dure), "IsSystemObject")
Dim soDrop As ScriptingOptions = New ScriptingOptions
soDrop.ScriptDrops = True
soDrop.IncludeIfNotExists = True
Dim sw As StreamWriter = New StreamWriter("c:\script.sql")
For Each sp As StoredProcedure In
SMOServer.Databases("northwind").StoredProcedures
If Not sp.IsSystemObject Then
For Each s As String In sp.Script(soDrop)
sw.WriteLine(s)
Next
End If
Next
Dim soCreate As ScriptingOptions = New ScriptingOptions
soCreate.Default = True
For Each sp As StoredProcedure In
SMOServer.Databases("northwind").StoredProcedures
If Not sp.IsSystemObject Then
For Each s As String In sp.Script(soCreate)
sw.WriteLine(s)
Next
End If
Next
sw.Close()
"Sergey Poberezovskiy" <SergeyPoberezovskiy@.discussions.microsoft.com> wrote
in message news:E68598F6-C496-4A84-A02F-B98CCDA2CC65@.microsoft.com...
> Hi,
> In SQL2000 there used to be an option to script selected database objects
> into separate files - cannot find this option in 2005 version.
> I used to use it to script all database objects and put them under VSS for
> easier management.
> Could anyone please tell where this option could be found, or what is the
> preferred way to script database objects into files (I have a database
> with
> about a hundred tables and a few hundred derived objects - views,
> functions,
> procs, etc.) I do not see it practical to create one file per object type.
> Any help is greatly appreciated.
|||http://www.sqlteam.com/item.asp?ItemID=23185
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sergey Poberezovskiy" <SergeyPoberezovskiy@.discussions.microsoft.com> wrote in message
news:E68598F6-C496-4A84-A02F-B98CCDA2CC65@.microsoft.com...
> Hi,
> In SQL2000 there used to be an option to script selected database objects
> into separate files - cannot find this option in 2005 version.
> I used to use it to script all database objects and put them under VSS for
> easier management.
> Could anyone please tell where this option could be found, or what is the
> preferred way to script database objects into files (I have a database with
> about a hundred tables and a few hundred derived objects - views, functions,
> procs, etc.) I do not see it practical to create one file per object type.
> Any help is greatly appreciated.

script objects into separate files in SQL 05

Hi,
In SQL2000 there used to be an option to script selected database objects
into separate files - cannot find this option in 2005 version.
I used to use it to script all database objects and put them under VSS for
easier management.
Could anyone please tell where this option could be found, or what is the
preferred way to script database objects into files (I have a database with
about a hundred tables and a few hundred derived objects - views, functions,
procs, etc.) I do not see it practical to create one file per object type.
Any help is greatly appreciated.Hi
http://www.nigelrivett.net/DMO/AddToSourceSafe.html
Add Reference to Microosft.SQLServer.ConnectionInfo
Add Reference to Microosft.SQLServer.SMO
Imports System.IO
Imports Microsoft.SqlServer.Management.Smo
...
Dim SMOServer As Server = New Server("WARDYIT01")
SMOServer.SetDefaultInitFields(GetType(StoredProcedure), "IsSystemObject")
Dim soDrop As ScriptingOptions = New ScriptingOptions
soDrop.ScriptDrops = True
soDrop.IncludeIfNotExists = True
Dim sw As StreamWriter = New StreamWriter("c:\script.sql")
For Each sp As StoredProcedure In
SMOServer.Databases("northwind").StoredProcedures
If Not sp.IsSystemObject Then
For Each s As String In sp.Script(soDrop)
sw.WriteLine(s)
Next
End If
Next
Dim soCreate As ScriptingOptions = New ScriptingOptions
soCreate.Default = True
For Each sp As StoredProcedure In
SMOServer.Databases("northwind").StoredProcedures
If Not sp.IsSystemObject Then
For Each s As String In sp.Script(soCreate)
sw.WriteLine(s)
Next
End If
Next
sw.Close()
"Sergey Poberezovskiy" <SergeyPoberezovskiy@.discussions.microsoft.com> wrote
in message news:E68598F6-C496-4A84-A02F-B98CCDA2CC65@.microsoft.com...
> Hi,
> In SQL2000 there used to be an option to script selected database objects
> into separate files - cannot find this option in 2005 version.
> I used to use it to script all database objects and put them under VSS for
> easier management.
> Could anyone please tell where this option could be found, or what is the
> preferred way to script database objects into files (I have a database
> with
> about a hundred tables and a few hundred derived objects - views,
> functions,
> procs, etc.) I do not see it practical to create one file per object type.
> Any help is greatly appreciated.|||http://www.sqlteam.com/item.asp?ItemID=23185
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sergey Poberezovskiy" <SergeyPoberezovskiy@.discussions.microsoft.com> wrote in message
news:E68598F6-C496-4A84-A02F-B98CCDA2CC65@.microsoft.com...
> Hi,
> In SQL2000 there used to be an option to script selected database objects
> into separate files - cannot find this option in 2005 version.
> I used to use it to script all database objects and put them under VSS for
> easier management.
> Could anyone please tell where this option could be found, or what is the
> preferred way to script database objects into files (I have a database with
> about a hundred tables and a few hundred derived objects - views, functions,
> procs, etc.) I do not see it practical to create one file per object type.
> Any help is greatly appreciated.

script objects into separate files in SQL 05

Hi,
In SQL2000 there used to be an option to script selected database objects
into separate files - cannot find this option in 2005 version.
I used to use it to script all database objects and put them under VSS for
easier management.
Could anyone please tell where this option could be found, or what is the
preferred way to script database objects into files (I have a database with
about a hundred tables and a few hundred derived objects - views, functions,
procs, etc.) I do not see it practical to create one file per object type.
Any help is greatly appreciated.Hi
http://www.nigelrivett.net/DMO/AddToSourceSafe.html
Add Reference to Microosft.SQLServer.ConnectionInfo
Add Reference to Microosft.SQLServer.SMO
Imports System.IO
Imports Microsoft.SqlServer.Management.Smo
...
Dim SMOServer As Server = New Server("WARDYIT01")
SMOServer. SetDefaultInitFields(GetType(StoredProce
dure), "IsSystemObject")
Dim soDrop As ScriptingOptions = New ScriptingOptions
soDrop.ScriptDrops = True
soDrop.IncludeIfNotExists = True
Dim sw As StreamWriter = New StreamWriter("c:\script.sql")
For Each sp As StoredProcedure In
SMOServer.Databases("northwind").StoredProcedures
If Not sp.IsSystemObject Then
For Each s As String In sp.Script(soDrop)
sw.WriteLine(s)
Next
End If
Next
Dim soCreate As ScriptingOptions = New ScriptingOptions
soCreate.Default = True
For Each sp As StoredProcedure In
SMOServer.Databases("northwind").StoredProcedures
If Not sp.IsSystemObject Then
For Each s As String In sp.Script(soCreate)
sw.WriteLine(s)
Next
End If
Next
sw.Close()
"Sergey Poberezovskiy" <SergeyPoberezovskiy@.discussions.microsoft.com> wrote
in message news:E68598F6-C496-4A84-A02F-B98CCDA2CC65@.microsoft.com...
> Hi,
> In SQL2000 there used to be an option to script selected database objects
> into separate files - cannot find this option in 2005 version.
> I used to use it to script all database objects and put them under VSS for
> easier management.
> Could anyone please tell where this option could be found, or what is the
> preferred way to script database objects into files (I have a database
> with
> about a hundred tables and a few hundred derived objects - views,
> functions,
> procs, etc.) I do not see it practical to create one file per object type.
> Any help is greatly appreciated.|||http://www.sqlteam.com/item.asp?ItemID=23185
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sergey Poberezovskiy" <SergeyPoberezovskiy@.discussions.microsoft.com> wrote
in message
news:E68598F6-C496-4A84-A02F-B98CCDA2CC65@.microsoft.com...
> Hi,
> In SQL2000 there used to be an option to script selected database objects
> into separate files - cannot find this option in 2005 version.
> I used to use it to script all database objects and put them under VSS for
> easier management.
> Could anyone please tell where this option could be found, or what is the
> preferred way to script database objects into files (I have a database wit
h
> about a hundred tables and a few hundred derived objects - views, function
s,
> procs, etc.) I do not see it practical to create one file per object type.
> Any help is greatly appreciated.

Script databsae does not compatible with SQL2000

I've created a databse on SQL2005, and I did not use any new data type for columns. I script the database, and select SQL2000 for "Script for Server version", howvever, the script generated cannot be run on SQL2000, it can be only run on SQL2005.

The script generated by SQL2005 is not backward compatible? like SQL2000 can generated script compatible wtih SQL7.

CREATE TABLE [dbo].[Table1](
[Col1] [nvarchar](16) NOT NULL,
[Col2] [nvarchar](100) NOT NULL,
[Col3] [nvarchar](10) NULL,
[Col4] [datetime] NULL,
[Col5] [nvarchar](10) NULL,
[Col6] [datetime] NULL,
[Col7] [bit] NULL CONSTRAINT [DF_Table1_Col7] DEFAULT ((0)),
[Col8] [bit] NULL CONSTRAINT [DF_Table1_Col8] DEFAULT ((0)),
[Col9] [int] NULL CONSTRAINT [DF_Table1_Col9] DEFAULT ((0)),
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Col1] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Error detected on the line "IGNORE_DUP_KEY = OFF".

This is a known bug. From the feedback center:

http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=a6510471-9c40-4184-9611-5656457864d8

Edit: I have tried it and it does seem to be fixed in the SP1 CTM

Louis

|||Generally, you should not rely on the scripting capabilities other than for ad-hoc stuff. It is better to maintain your scripts in a source code control system and use it for reference / maintenance. There are other problems with scripting other than syntax issue with the CONSTRAINT in your example. For example, resolving dependencies is not accurate since the engine itself doesn't guarantee it for all cases. There are also issues with expressions which are normalized / modified by the engine so if you try to compare the script generated from the engine with your source it will not match even though they produce identical results.|||I'm sorry, but I can't hold back. The condescension in your reply to this stranded user is almost palpable. These types of responses are absolutely maddening, especially when the bugs in the SQL Server 2005 tools are so *painfully* obvious upon even casual observation. People don't need lectures on why you shouldn't be using a feature YOU included in the server which doesn't work because YOU did incredibly inadequate testing before you shipped. Seriously, just about any script you create from SQL Mgmt Studio which you want to run on 2000 will not work because it uses sys.objects. That bug should have never been included in the release, and frankly is a sign of a real management problem inside your team which needs to be addressed.|||

I won't dispute the 2005 tools have errors comment, and neither did he (in fact I would sing in that chorus with you if need be). However, he post was not a "well, you shouldn't be using it anyway" comment, I don't think. He goes on to explain that some parts of scripting out objects is just really quite hard (like maintaining precedence of which script to run first.)

As a side note, the sys.objects issue has been changed in the CTP as well for the 2000 compatible scripting.

All he was trying to say was that the best way to use the tools and do development is to maintain scripts of your work, and not rely on the tools to script out a database. I doubt that he was suggesting to never use the scripting tools for any reason. (especially since he advocated it for ad-hoc stuff.) I know I use it quite often, if for no other reason than to post a table structure to a discussion. But for a production system you should generally should have scripts for all objects that were created outside of using the SSMS tools and then scripting the objects.

It is very common to include a mini-lecture with any post where someone sees that a person *might* be abusing a feature. I learned a ton from my early days in the newsgroups because a friendly user or two (and an unfriendly albeit, very intelligent, jerk) lectured me on how something should be done.

|||Thanks, ***. Fix your God damned product or don't ship it. SQL 2005 is ***.