Compatibility of SQL 2000

I find that the "Create Table" script generated by SQL Server 2005 is in format:

CREATE TABLE [dbo].[City](
[CityID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED
(
[CityID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Can this appliable in SQL Server 2000 or MSDE? If not, how should I change it to make it work in both SQL 2000 and 2005?

Thanks

Thats not working in SQL 2000. You will have to create it in compat. mode 2k.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Would you please show me a sample code?

Thanks

|||Hi,

the equivalent would be:

CREATE TABLE [dbo].[City](

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

[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED

(

[CityID] ASC

) ON [PRIMARY]

)

GO

CREATE UNIQUE INDEX [SomenewIndex] ON [dbo].[City]([CityID])

WITH IGNORE_DUP_KEY ON [PRIMARY]

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

What did you use to generate the script above? Did you use SMO or Tools? Did you select the correct compatibility level for the script? If you cannot get the correct output for the script then you should just create it by hand since it is easier that way and avoids all the other unnecessary default options. For example, the default for IGNORE_DUP_KEY is OFF for PRIMARY/UNIQUE key constraints and you should probably avoid using it anyway. So you can write DDL like:

CREATE TABLE [dbo].[City](
[CityID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED
(
[CityID] ASC
)

) ON [PRIMARY]

Btw, if you cannot get the tools or SMO to work correctly then please file a bug using http://connect.microsoft.com/sqlserver or create a thread in the SQL Server Tools forum.

|||

Thanks for the reply.

I generated the script using SMO in the following steps:

1. In SMO, right-click on the table City, choose Modify.

2. Highlight CityID, click the "Set Primary Key" button and save the change

3. Right-click on table City, choose "Script table as", choose "Create to"....

Thanks.

|||

Hi

u can't use ignore_dup_key with constraint. u need to create index key.

CREATE TABLE [dbo].[test] (
[id] [int] NOT NULL ,
[name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[test] WITH NOCHECK ADD
CONSTRAINT [pk_id] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO

CREATE UNIQUE INDEX [ind_id] ON [dbo].[test]([id] DESC ) WITH IGNORE_DUP_KEY ON [PRIMARY]
GO

|||

I upgraded my SQL Server 2005 to SP1.

Then this line : WITH (IGNORE_DUP_KEY = OFF) is removed from generated script.

Problem solved!!