Question on sp_create_plan_guide

Mikhail Firsov 1,876 Reputation points
2021-08-25T15:03:13.77+00:00

Hello,

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

Step 1 16:57
126421-q1.png

Step 2 16:57
126412-q2.png

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

Step 4 = Step 2 16:10

126422-q3.png

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

Step 5: From now on no new plans can be created:
126423-q4.png

Q2: ???

Thank you in advance,
Michael

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,562 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Vladimir Moldovanenko 251 Reputation points
    2021-08-25T16:34:59.513+00:00

    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

    0 comments No comments

  2. Mikhail Firsov 1,876 Reputation points
    2021-08-26T07:36:37.64+00:00

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

    126599-q5.png

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


  3. Mikhail Firsov 1,876 Reputation points
    2021-08-26T13:07:12.28+00:00

    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
    
    0 comments No comments