July 22, 2009 at 2:38 am
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;
July 22, 2009 at 2:56 am
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!
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...)
July 22, 2009 at 3:09 am
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
July 22, 2009 at 3:53 am
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
July 22, 2009 at 6:33 am
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
July 22, 2009 at 8:19 pm
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]
July 22, 2009 at 9:52 pm
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
Change is inevitable... Change for the better is not.
July 23, 2009 at 1:25 am
thanks for valuable suggestion,
table created sucessfully, do i need to change something in stored procedure ,views etc code also,
July 23, 2009 at 2:22 am
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
July 23, 2009 at 5:35 am
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
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply