how to degrade a database schema from 2005 to 200

Question

how to degrade a database schema from 2005 to 200

Saurabh Aggarwal

Ten Centuries

Points: 1073
More actions
July 22, 2009 at 2:38 am

#203069

how to degrade a database schema from 2005 to 200.
I have a request in hand to copy the database objects from 2005 server to 2000 database, I have tried to implement it through right click and generate scripts but when it ran on 2000 server it has given errors...
Pls advicel;

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Vegard Hagen Hall of Fame Points: 3078 More actions · Answer 1

You can't include any system objects because 2005 system objects are not supported by 2000. If you allow 2000 to create its own system objects and copy only the user objects from the 2005 server, then there is a chance this might work. I can't make any promises though. Good luck!

Kind regards,

Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)

It is better to light a candle than to curse the darkness. (Chinese proverb)

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

In the Generate Scripts wizard, on the options page, change the server version for the script to SQL 2000.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Saurabh Aggarwal Ten Centuries Points: 1073 More actions · Answer 3

Hi Thanks for your reply, I have tried that option by selecting 2000 version, but when it ran on sql server 2000 it says error and not able to proceed furthur, please advice

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

I can't offer useful advice without knowing what the error was and on what command it was raised...

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Saurabh Aggarwal Ten Centuries Points: 1073 More actions · Answer 5

Hi see I am running the below code after extracting by script and it gives me error msg

incorrect syntax near ")"

Server: Msg 170, Level 15, State 1, Line 8

Line 8: Incorrect syntax near '('.

=================================================

CREATE TABLE [dbo].[LI_TradeStructure](

[LI_TradeStructure_ID] [varchar](50) NOT NULL CONSTRAINT [DF_LI_TradeStructure_LI_LI_TradeStructure_ID] DEFAULT (newid()),

[LI_TradeStructureLITE_ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

[LI_TradeStructure_Name] [varchar](50) NOT NULL,

CONSTRAINT [PK_LI_TradeStructure] PRIMARY KEY CLUSTERED

(

[LI_TradeStructure_ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,

ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [defgrp]

) ON [defgrp]

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 6

Remove all of the WITH hints except for the Fill Factor.

I can't believe this isn't handled automatically when you tell it to gen the script as SQL Server 2000.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Saurabh Aggarwal Ten Centuries Points: 1073 More actions · Answer 7

thanks for valuable suggestion,

table created sucessfully, do i need to change something in stored procedure ,views etc code also,

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 8

Saurabh Aggarwal (7/23/2009)
do i need to change something in stored procedure ,views etc code also,

Maybe. Are they failing to create too?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 9

Jeff Moden (7/22/2009)
I can't believe this isn't handled automatically when you tell it to gen the script as SQL Server 2000.

It is, at least in SP3. I generated a script of a table, set the target server version to SQL 2000 and this is what I got out.

CREATE TABLE [dbo].[Threads](

[ThreadID] [int] IDENTITY(1,1) NOT NULL,

[ForumID] [int] NULL,

[Title] [varchar](500) NULL,

[CreatedOn] [datetime] NULL,

[LastModified] [datetime] NULL,

[CreatedBy] [int] NULL,

[LastPoster] [int] NULL,

[TotalReplies] [smallint] NULL,

[TotalViews] [int] NULL,

[Locked] [bit] NULL,

[RestrictedReplies] [bit] NULL,

CONSTRAINT [PK_Threads] PRIMARY KEY CLUSTERED

(

[ThreadID] ASC

)WITH FILLFACTOR = 90 ON [PRIMARY]

) ON [PRIMARY]

GO

Tried again with target server version set to SQL 2005 and got this

CREATE TABLE [dbo].[Threads](

[ThreadID] [int] IDENTITY(1,1) NOT NULL,

[ForumID] [int] NULL,

[Title] [varchar](500) NULL,

[CreatedOn] [datetime] NULL,

[LastModified] [datetime] NULL,

[CreatedBy] [int] NULL,

[LastPoster] [int] NULL,

[TotalReplies] [smallint] NULL,

[TotalViews] [int] NULL,

[Locked] [bit] NULL,

[RestrictedReplies] [bit] NULL,

CONSTRAINT [PK_Threads] PRIMARY KEY CLUSTERED

(

[ThreadID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

) ON [PRIMARY]

GO

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass