Microsoft SQL Service Management Studio 2005 '

Question

When using the 'Script Index as CREATE To' to duplicate an index from one table to another table I am not able to run the script. Without editing the generated code I get an error. I get the same error when working with any 'Script XXX as CREATE To' generated code. Any comments/suggestions to solve this problem?

ERROR:

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

SCRIPT:

CREATE NONCLUSTERED INDEX [IX_IncidentStop_Note] ON [dbo].[IncidentStop_Note] 
(
    [IncidentStopID] ASC,
    [NoteID] ASC,
    [Active_Fl] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

EDIT:

As from the answer from Stuart Ainsworth it turns out I am in SQL Server 2000 (80) Compatibility level. My question would slightly change to how can I duplicate something from one table to another while working in 2005 but having the compatibility level of 2000?

Answer 1

What's the compatibility mode or server level set to for the destination database? Is it SQL 2000 (or compatibility mode 80)? If so, then the WITH option will not run; the syntax generated by the SQL 2005 Mgmt Studio is 2005 compliant (even if you issue it against a SQL 2000 server or 2000-compliant database).