Query works in SQL Server 2005 but syntax error for SQL Server 2000

Question

I am trying to execute this query but getting syntax error when it perfectly fines with SQL server 2005 and table is already there and am trying to create new table on a different server,

USE [myDataBase]
GO

CREATE TABLE [dbo].[myTable]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Tittle] [varchar](1024) NOT NULL,
    [Description] [varchar](8000) NOT NULL,
    [Table2ID] [int] NOT NULL,
    [Table3ID] [int] NOT NULL,
 CONSTRAINT [PK_myTable] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)



WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO

Error

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

In above example, Line 16 is empty and am so confused.

I have not worked on 2000 so I don't know the difference. But just remove Schema before Table name i.e. change [dbo].[myTable] to [myTable] — Romesh
– Romesh, Commented Jun 19, 2013 at 10:54
Try not to pay too much attention to the line numbers given to you. its not like VS 2010 where it will pinpoint the exact line, think of them as more a general guide lol — Dev N00B
– Dev N00B, Commented Jun 19, 2013 at 11:05
@DevN00B If running the query in SSMS then double clicking on the error message should generally take you to the offending line though. It is the 16th line in the batch (18th overall) — Martin Smith
– Martin Smith, Commented Jun 19, 2013 at 11:08

marc_s · Accepted Answer · 2013-06-19 11:10:20Z

5

WITH in SQL Server 2000 allows only FillFactor to be set. See the documentation:

http://msdn.microsoft.com/en-us/library/aa258255%28v=sql.80%29.aspx

Remove other options from the WITH clause.

edited Jun 19, 2013 at 11:10

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jun 19, 2013 at 10:54

Jakub Konecki

46.1k8 gold badges92 silver badges128 bronze badges

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

Collectives™ on Stack Overflow

Query works in SQL Server 2005 but syntax error for SQL Server 2000

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related