question

MikhailFirsov-1277 avatar image
0 Votes"
MikhailFirsov-1277 asked Viorel-1 edited

Question on sp_create_plan_guide

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


sql-server-transact-sql
q1.png (27.7 KiB)
q2.png (41.9 KiB)
q3.png (225.9 KiB)
q4.png (27.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

VladimirMoldovanenko-3700 avatar image
0 Votes"
VladimirMoldovanenko-3700 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MikhailFirsov-1277 avatar image
0 Votes"
MikhailFirsov-1277 answered Viorel-1 edited

"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!


q5.png (24.3 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @MikhailFirsov-1277,

Can you provide specific code instead of screenshots?

Regards
Echo

0 Votes 0 ·

Probably COMPUTERS has a column called Server.

The suggested quotes can be used like this:

@stmt = N'SELECT * FROM COMPUTERS where ComputerType = ''Server''',

or

@stmt = N'SELECT * FROM COMPUTERS where ComputerType = ''Notebook''',


0 Votes 0 ·
MikhailFirsov-1277 avatar image
0 Votes"
MikhailFirsov-1277 answered MikhailFirsov-1277 edited

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.