July 22, 2009 at 9:59 am
Hey there fellow SQL friends,
I have been scratching my head over this issue for the past few days, and was wondering if anyone has ever encountered this problem & possibly shed some light on this - here is the sql I'm running to create a plan guide:
exec sp_create_plan_guide
@name = N'TestPlanGuide1',
@stmt = N'SELECT top 1000 ColumnName FROM [DBName].[dbo].[TableName] with (nolock)
WHERE (ColumnName < Criteria)',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (TABLE HINT (INDEX = IndexName, NOLOCK))';
and here's the error message I get:
Msg 1018, Level 15, State 1, Line 1
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.
Msg 10504, Level 16, State 1, Line 1
Cannot create plan guide 'TestPlanGuide1' because parameter @hints is incorrect. Use N'OPTION (
Any help is appreciated!
July 23, 2009 at 1:00 am
According to MS BOL , OPTION Caluse will have QueryHint
query_hint ::=
{ { HASH | ORDER } GROUP
| { CONCAT | HASH | MERGE } UNION
| { LOOP | MERGE | HASH } JOIN
| FAST number_rows
| FORCE ORDER
| MAXDOP number_of_processors
| OPTIMIZE FOR ( @variable_name = literal_constant [ ,…n ] )
| PARAMETERIZATION { SIMPLE | FORCED }
| RECOMPILE
| ROBUST PLAN
| KEEP PLAN
| KEEPFIXED PLAN
| EXPAND VIEWS
| MAXRECURSION number
| USE PLAN N'xml_plan'
}
exec sp_create_plan_guide
@name = N'TestPlanGuide1',
@stmt = N'SELECT top 1000 ColumnName FROM [DBName].[dbo].[TableName](INDEX = IND1,NOLOCK)
WHERE (ColumnName < Criteria)',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (Query Hint)';
Pl try now
July 23, 2009 at 11:26 am
Finally figured it out. It's like this:
exec sp_create_plan_guide
@name = N'TestPlanGuide1',
@stmt = N'SELECT top 1000 ColumnName FROM [DBName].[dbo].[TableName] with (nolock)
WHERE (ColumnName < Criteria)',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (TABLE HINT ([DBName].[dbo].[TableName], INDEX = IndexName, NOLOCK))';
I knew it was something trivial...thanks Pandian S =)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply