How to Transfer database from mssql2005 to mssql2000 by using generate script.

Currently i need to transfer database (complete with primary key, foreign key ,constraint) from mssql2005 to mssql2000 by using generate script. After getting the script, i execute the script in the sql analyzer 2000, i get a lots of syntax error. Following are some of the error :

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.
Server: Msg 170, Level 15, State 1, Line 17
Line 17: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 25
Line 25: Incorrect syntax near '('.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.
Server: Msg 170, Level 15, State 1, Line 15
Line 15: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 17
Line 17: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 14
Line 14: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 99
Line 99: Incorrect syntax near '('.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.
Warning: The table 'HACOMP_TMP' has been created but its maximum row size (16427) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.
Server: Msg 170, Level 15, State 1, Line 16
Line 16: Incorrect syntax near '('.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.
Warning: The table 'HAAPPRAISE_DEV_TMP' has been created but its maximum row size (9196) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

Below are the first part of the script :

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HRMP_Details]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[HRMP_Details](
[Comp_CD] [smallint] NULL,
[MP_ID] [numeric](10, 0) IDENTITY(1,1) NOT NULL,
[MP_Code] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Replacement] [bit] NOT NULL,
[Employee_Replaced] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Terminate_Date] [int] NULL,
[Addition] [bit] NULL,
[Approved_Budget] [bit] NULL,
[MP_Permanent] [bit] NULL,
[MP_Temporary] [bit] NULL,
[Time_Needed] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MP_Contract] [bit] NULL,
[Contract_Period] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Full_Time] [int] NULL,
[Part_Time] [int] NULL,
[Hours_Required] [int] NULL,
[Status] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Hire_By] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Headcounts] [int] NULL,
[Approved_Headcounts] [int] NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Applicant_Score]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Applicant_Score](
[COMP_CD] [smallint] NOT NULL,
[Score_ID] [int] IDENTITY(1,1) NOT NULL,
[Applicant_ID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MP_Code] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Dimension_Code] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Interview_Code] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Interviewer] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Score] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Comment_Number] [int] NULL,
[ID] [int] NULL,
CONSTRAINT [PK_Applicant_Score] PRIMARY KEY CLUSTERED
(
[Score_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Advertisement]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Advertisement](
[COMP_CD] [smallint] NOT NULL,
[Advertisement_ID] [int] IDENTITY(1,1) NOT NULL,
[MP_Code] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Advertisement_Through_Internet] DEFAULT ((0)),
[Channel_Code] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Languages] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Advertisement_StartDate] [int] NULL,
[Advertisement_EndDate] [int] NULL,
[Monday] [bit] NULL,
[Tuesday] [bit] NULL,
[Wednesday] [bit] NULL,
[Thursday] [bit] NULL,
[Friday] [bit] NULL,
[Saturday] [bit] NULL,
[Sunday] [bit] NULL,
[Daily] [bit] NULL,
[Instruction] [varchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[User_id] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Date_Time] [datetime] NULL,
CONSTRAINT [PK_Advertisement] PRIMARY KEY CLUSTERED
(
[Advertisement_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HAAppraise_COMPETENCY_Approve]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[HAAppraise_COMPETENCY_Approve](
[COMP_CD] [smallint] NOT NULL,
[COMPETENCY_CODE] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[COMPETENCY_ID] [int] IDENTITY(1,1) NOT NULL,
[COMPETENCY_DESC] [nvarchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EmpNo] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Appraisal_Code] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[App_Form_Code] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[User_Login] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Date_Time] [datetime] NULL,
[Competency_Tracking] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Competency_Type] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
END

Can anyone help me in this?

ps:I cant use the import/export utility because using this method does not include some of the primary key,foreign key, constraint and etc.

You can't script out and transfer the entire database.

There are incompatibilities between SQL 2000 and SQL 2005 schemas. (For example, there is not a [sys.objects] table in SQL 2000, SQL 2005 contains datatypes [such as (varchar(max)] that are not allowed in SQL 2000, etc.

However, you can script out specific data tables and transfer them to a SQL 2000 database as long as the datatypes are supported in SQL 2000, and there is not the use of any CONSTRAINT 'code' that is incompatible.

I suggest that you use the IMPORT/EXPORT wizard, or explore some of the third party products that will create scripts.

|||Using SSMS you can use the Create Script wizard which also support the creation of compatible scripts. Run the script after creating it to create the new database. Afterwards you can transfer the data(base) to the new database.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||Hi, using Sql Server 2005 Developer Edition Service Pack 2, right clicking on an existing database, selecting Generate Script and using the option to generate scripts for Sql 2000 does NOT prevent the generated script from containing "max". Is there a set of guidelines somewhere on what types/objects to avoid if you want to create a schema that is truly Sql Server 2000 compatible please?
|||

The (max) types cannot be converted to SQL Server 200 using the compat script, you will have to tweak the script on your own, changing the max to the appropiate types like text/image.

Jens K. Suessmeyer

http://www.sqlserver2005.de