Question on sp_create_plan_guide

Question

Hello,

...been trying to create a test plan guide:

Step 1 16:57

Step 2 16:57

Step 3 16:58 - 16:09 - digressed for ~12 minutes to read email and documents

Step 4 = Step 2 16:10

Q1: How come my newly-created plan guide has disappeared by itself?

Step 5: From now on no new plans can be created:

Q2: ???

Thank you in advance,
Michael

Answer 1

You are missing quotes in your statement around 'Notebook'

SELECT * FROM dbo.COMPUTERS where COMPUTERS.ComputerType = 'Notebook'

also it's N'Notebook' if COMPUTERS.ComputerType is nvarchar

Answer 2

"You are missing quotes in your statement around 'Notebook'":

..."Notebook" produces the same error as if there're no quotes at all.
Anyway, that same command did succeed 12 minutes ago!

Answer 3

Hi EchoLiu-msft,

Yes, of course - here it is:

EXEC sp_create_plan_guide
@name = N'GETCOMPUTERS_SERVERS',
@stmt = N'SELECT * FROM COMPUTERS where COMPUTERS.ComputerType = Server',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (RECOMPILE)'
GO

SELECT * FROM sys.plan_guides
GO