Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Bex
Aged Yak Warrior
580 Posts |
 Posted - 2006-08-09 : 12:00:23
|
Hello allI am trying to create a script to create a number of indexes. However, I keep getting an error even though I am following the syntax in BOL. Can anyone please offer any help on this:This is the Query I have created:CREATE NONCLUSTERED INDEX nc_LineItem_OrderLineID ON oms.TescoDirect.LineItem (OrderLineID ASC)WITH ( STATISTICS_NORECOMPUTE = ON );ON TescoDirect_Index Here is an example query in BOL (which throws the same error):CREATE NONCLUSTERED INDEX IX_WorkOrder_ProductID ON Production.WorkOrder(ProductID) WITH (FILLFACTOR = 80, PAD_INDEX = ON, DROP_EXISTING = ON); Here is the error message:Msg 170, Level 15, State 1, Line 4Line 4: Incorrect syntax near '('.Thanks in advanceHearty head pats |
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2006-08-09 : 14:24:13
|
No need for the '= ON'Possible BOL errata?edit:in BOL review:<relational_index_option> vs. <backward_compatible_index_option>Seems to be a 2000 vs 2005 issue. CREATE NONCLUSTERED INDEX [nc_LineItem_OrderLineID] ON oms.TescoDirect.LineItem ( OrderLineID ASC )WITH STATISTICS_NORECOMPUTEON [TescoDirect_Index]; Nathan Skerl |
 |
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2006-08-09 : 14:41:04
|
| Yep, you are absolutely right! Thankyou very much!! Seems rather strange though as I am working on SQL Server 2005 and not 2000, but the new syntax doesn't work, yet the old skool style does!! Stick to old skool!!!Hearty head pats |
|
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2006-08-09 : 14:50:40
|
quote:
as I am working on SQL Server 2005 and not 2000...
Check your compatibility level. Your 2005 box may be set to 80 instead of 90. I think it defaults to 80 upon install.Of course, change the compatibility level in DEV first!  Nathan Skerl |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-09 : 15:02:23
|
quote:
Originally posted by nathans
quote:
as I am working on SQL Server 2005 and not 2000...
Check your compatibility level. Your 2005 box may be set to 80 instead of 90. I think it defaults to 80 upon install.Of course, change the compatibility level in DEV first! Nathan Skerl
It does this on the restore. When you restore a 2000 database to a 2005 server, it keeps the compatibility level to what the source has. This is true in all other versions as well. For example, if you wanted to upgrade your database from 7.0 to 2000, you'd need to change the compatibility level to 80 after the restore if you wanted to use 2000 features. So for 2005, you'd want to change it to 90 to use 2005 features.Tara Kizer |
|
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2006-08-09 : 15:18:11
|
Ah, good looking out on Microsoft's part  Thanks for the info TaraNathan Skerl |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2006-08-17 : 14:24:10
|
quote:
Originally posted by tkizer
quote:
Originally posted by nathans
quote:
as I am working on SQL Server 2005 and not 2000...
Check your compatibility level. Your 2005 box may be set to 80 instead of 90. I think it defaults to 80 upon install.Of course, change the compatibility level in DEV first! Nathan Skerl
It does this on the restore. When you restore a 2000 database to a 2005 server, it keeps the compatibility level to what the source has. This is true in all other versions as well. For example, if you wanted to upgrade your database from 7.0 to 2000, you'd need to change the compatibility level to 80 after the restore if you wanted to use 2000 features. So for 2005, you'd want to change it to 90 to use 2005 features.Tara Kizer
DB compatibility level is 90. Bex - nice to see you here *##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
|
|
|
|
|
Error creating index script in SQL Server 2005