Share via

Question on sp_create_plan_guide

Mikhail Firsov 1,881 Reputation points
Aug 25, 2021, 3:03 PM

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,665 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Vladimir Moldovanenko 276 Reputation points
    Aug 25, 2021, 4:34 PM

    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,881 Reputation points
    Aug 26, 2021, 7:36 AM

    "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,881 Reputation points
    Aug 26, 2021, 1:07 PM

    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

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.