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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 INCORRECT SYNTAX NEAR 'INDEX'. sql 2008

Author  Topic 

urfriend
Starting Member

4 Posts

Posted - 2014-10-01 : 02:38:17
Can someone assist me ?
1. SELECT * FROM [01_DB].[dbo].tble (INDEX=IX_tble_DOC)
2. SELECT * FROM [14_DB].[dbo].tble (INDEX=IX_tble_DOC)
excute on the same server (SQL 2008 server) : (1) run OK , but (2) output error :
"Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax."
I know need key word WITH : WITH (INDEX=IX_tble_DOC)
but i don't understand why (1) run OK without WITH keyword), maybe because of the database , so what do I do to run the query without "WITH" keyword on SQL 2008 Server ? is it possible ?
Thanks !!!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2014-10-01 : 05:50:50
I believe both of these databases are using different versions of sql server. Please check the compatibility level for both the databases. From SQL Server 2005 onwards, you need to use WITH clause if you need to specify query hints (although some hints are excluded from this requirement)

Harsh Athalye
http://in.linkedin.com/in/harshathalye/
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-10-01 : 11:18:27
quote:
Omitting the WITH keyword is a deprecated feature: This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

See http://msdn.microsoft.com/en-us/library/ms187373(v=sql.100).aspx



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-10-01 : 15:21:45
Don't even try to avoid WITH, it's technically always been the only really correct way to code a hint. Instead, start adding "WITH" everywhere you are using such hints.
Go to Top of Page

urfriend
Starting Member

4 Posts

Posted - 2014-10-02 : 03:54:32
thanks for reply !!!
I'm sure that these database are on the same server (SQL server 2008) . I created them.
I ran the queries again and again (many times) : (1) OK, (2) error incorrect syntax.....
the reason that I don't want to use "With" is my software is an old software (run with SQL 2000) and I can not fix it, but now I want to upgrade server (SQL 2000 --> 2008)... It's problem...
Go to Top of Page
   

- Advertisement -