Error Message:
Msg 10502, Level 16, State 1, Line 1
Cannot create plan guide ‘%.*ls’ because the statement specified by @stmt and @module_or_batch, or by @plan_handle and @statement_start_offset, matches the existing plan guide ‘%.*ls’ in the database. Drop the existing plan guide before creating the new plan guide.
Severity level:
16.
Description:
This error message appears when you try to create a planguide, but the statement specified by @stmt and @module_or_batch parameter matches an existing plan.
Consequences:
The T-SQL statement can be parsed, but causes the error at runtime.
Resolution:
Errors of the Severity Level 16 are generated by the user and can be fixed by the SQL Server user. The statement cannot be executed this way. You must first remove the existing planguide for a statement, before you can create a new one.
Versions:
This error message was introduced with SQL Server 2005.
Example(s):
IF OBJECT_ID (‘dbo.t’) IS NOT NULL
DROP TABLE dbo.t;
GO
CREATE TABLE dbo.t
(
c1 int
);
GO
EXEC sp_create_plan_guide
@name = ‘Guide1’,
@stmt = ‘SELECT TOP 1
*
FROM
dbo.t
ORDER BY
c1 DESC’,
@type = ‘SQL’,
@module_or_batch = NULL,
@params = NULL,
@hints = ‘OPTION (MAXDOP 1)’;
EXEC sp_create_plan_guide
@name = ‘Guide1’,
@stmt = ‘SELECT TOP 1
*
FROM
dbo.t
ORDER BY
c1 DESC’,
@type = ‘SQL’,
@module_or_batch = NULL,
@params = NULL,
@hints = ‘OPTION (MAXDOP 1)’;
–EXEC sp_control_plan_guide ‘DROP’, ‘Guide1’;
Remarks:
In the above example we try to create a planguide. Because the statement in the second procedure call matches the one from the first, the error is raised.