Syntax error using primary key script generated in SSMS

Question

I'm trying to move a primary key from one database (SQL Server 2000) to another database. In the original database I have a primary key that I've used SSMS to script this to a new query window:

USE [DatabaseName_Test]
GO

ALTER TABLE [dbo].[tbl_itinerary_item] ADD  CONSTRAINT [PK_tbl_itinerary_item] PRIMARY KEY CLUSTERED 
(
    [activity_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

All I did was change the database:

USE [DatabaseName]
GO

ALTER TABLE [dbo].[tbl_itinerary_item] ADD  CONSTRAINT [PK_tbl_itinerary_item] PRIMARY KEY CLUSTERED 
(
    [activity_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

The error I get is

Msg 170, Level 15, State 1, Line 5 Line 5:
Incorrect syntax near '('.

What am I missing?

possible duplicate of Convert Single SQLSERVER 2005 Script to a SQL SERVER 2000 script — Pondlife
– Pondlife, Commented Jan 15, 2013 at 20:20
AFAIK this part is understood on SQLServer 2005 for the first time : WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) — bummi
– bummi, Commented Jan 15, 2013 at 20:23
@Macness Yes, this question is actually an even better one to look at, because it gives a solution too (how to tell SSMS to generate SQL2000-compatible scripts). — Pondlife
– Pondlife, Commented Jan 15, 2013 at 20:43

user1151835 · Accepted Answer · 2013-01-15 20:24:20Z

1

Try to simplify your sql like this:

ALTER TABLE [dbo].[tbl_itinerary_item] ADD  CONSTRAINT [PK_tbl_itinerary_item] PRIMARY KEY 
(
    [activity_id] ASC
)ON [PRIMARY]
GO

answered Jan 15, 2013 at 20:24

user1151835

Sign up to request clarification or add additional context in comments.

1 Comment

Macness Over a year ago

That certainly did the trick. The script was overcomplicating things. I work in both a SQL 2000 and 2008 R2 environment so the script seems to be more in line with options that can be used by 2005. I think

Collectives™ on Stack Overflow

Syntax error using primary key script generated in SSMS

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related