Convert Single SQLSERVER 2005 Script to a SQL SERVER 2000 script

Question

Hi guys first of all I have no expirience with DB programing only basic SQL queries.

I have an sql script that was devolped for SQL Server 2005 FB and I need to run it in SQL server 2000.

I don't have the original 2005 DB. only a script and a SQL 2000DB

I found many guides for converting a whole DB but what can I do with a single script?

it is a very long script that I cannnot share here because of datasecurity issues.

here is the error

enter image description here please assist me.

If you feel you cannot post it here, why not just show us the portions of the code where it fails when you try to run under SQL2000? — Jan Doggen
– Jan Doggen, Commented Mar 29, 2012 at 12:38
Probably it will directly work without changes. Or it will not. But if you don't try, and don't show it to us, how could we help you??? — JotaBe
– JotaBe, Commented Mar 29, 2012 at 12:39

Marek Grzenkowicz · Accepted Answer · 2012-03-29 13:34:36Z

2

The syntax for specifying index options has changed - look closely at the WITH clause:

SQL Server 2000

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
[ WITH < index_option > [ ,...n] ]
[ ON filegroup ]

SQL Server 2005 and later

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON <object> ( column [ ASC | DESC ] [ ,...n ] ) 
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]
    [ ON { partition_scheme_name ( column_name ) 
         | filegroup_name 
         | default 
         }
    ]
[ ; ]

The WITH clauses in your script use parentheses and that causes the error. Unless you know that the script sets some specific index options, you can simply remove all the WITH ( ... ) clauses.

edited Mar 29, 2012 at 13:34

answered Mar 29, 2012 at 13:23

Marek Grzenkowicz

17.4k11 gold badges88 silver badges118 bronze badges

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

Collectives™ on Stack Overflow

Convert Single SQLSERVER 2005 Script to a SQL SERVER 2000 script

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