SQL error with script to create

Question

I used the SQL Server management studio on a table with Create Script to New and did minor changes. Give me an error "Incorrect syntax near '('" for the "(" after "WITH"

/* EventType Table Creation */

CREATE TABLE [EventType]
(
 [pkEventID]  [int] IDENTITY(1,1) NOT NULL,
 [Description] [nvarchar](50) NOT NULL,
 [BeginDate]  [datetime] NOT NULL,
 [EndDate]  [datetime] NOT NULL,
 [Comments]  [nvarchar](500) NOT NULL,
 CONSTRAINT [PK_EventType] PRIMARY KEY 
 CLUSTERED 
 (
 [pkEventID] ASC
 )
 WITH 
 (
  PAD_INDEX = OFF, 
  STATISTICS_NORECOMPUTE = OFF, 
  IGNORE_DUP_KEY = OFF, 
  ALLOW_ROW_LOCKS = ON, 
  ALLOW_PAGE_LOCKS = ON
 ) 
 ON [PRIMARY]
)
ON [PRIMARY]
GO

Bill Karwin · Accepted Answer · 2009-04-15 21:05:51Z

5

Which version of Microsoft SQL Server are you executing this CREATE TABLE statement against? According to documentation, MS SQL Server 2000 does not recognize the syntax for WITH (...index options...). That syntax is supported in MS SQL Server 2005 and later.

Even if you use SQL Managment Studio 2005, you may be connecting to MS SQL Server 2000. To verify the version, try this query:

SELECT  SERVERPROPERTY('productversion'), 
  SERVERPROPERTY ('productlevel'), 
  SERVERPROPERTY ('edition');

MS SQL Server 2000's productversion is 8.x.

MS SQL Server 2005's productversion is 9.x.

MS SQL Server 2008's productversion is 10.x.

edited Apr 15, 2009 at 21:05

answered Apr 15, 2009 at 20:54

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

3 Comments

PapaDaniel Over a year ago

SQL Management Studio 2005 - again, the tool generated the code and then complained about it :)

Bill Karwin Over a year ago

OK, but what's the version of the MS SQL Server you're connecting to from Management Studio 2005?

PapaDaniel Over a year ago

The server I am accesing shows 8.0.2039 as the version which is 2000. The fact that I am using a 2005 tool created the "Create" script without taking that in to account. Thanks.

Collectives™ on Stack Overflow

SQL error with script to create

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related