Transact SQL – Plan Guide created with name other than supplied name

Background

About a week ago, I ran into a problem where a plan guide was created with a name other than name originally supplied.

 

Code


set quoted_identifier off;
set XACT_ABORT on;
go

use [msdb]
go

declare
 	  @id				int
	, @planGuideName	nvarchar(128)
	, @database			sysname
	, @queryText		nvarchar(4000)
	, @planType			sysname
	, @module_or_batch	sysname
	, @hint				sysname

	DECLARE @templatetext			nvarchar(max);
	DECLARE @parameters				nvarchar(max);

	DECLARE @SQLTemplate						varchar (4000)
	declare @ERR_sp_get_query_templatefailed	varchar (4000)

	declare @errBuffer							varchar(4000)

	set @ERR_sp_get_query_templatefailed = 'sp_get_query_template failed for query %s'

	begin tran

		--set @planGuideName = N'msdb dbo sysjobactivity set data for specific Job & Session ID'
		set @planGuideName = N'msdb.dbo.sysjobactivity set data for specific Job & Session ID'
		set @queryText     = 'UPDATE msdb.dbo.sysjobactivity SET run_requested_date = DATEADD(ms, -DATEPART(ms, GetDate()),  GetDate()), run_requested_source = 1, queued_date = NULL, start_execution_date = NULL, last_executed_step_id = NULL, last_executed_step_date = NULL, stop_execution_date = NULL, job_history_id = NULL, next_scheduled_run_date = NULL WHERE job_id = 0x0F11A965B531294D9040BFED4205424E and session_id = 121'
		set @planType	   = N'TEMPLATE'
		set @hint		   =  N'OPTION(PARAMETERIZATION FORCED)'

		select
				[@@version] = @@version

		/*
			Get Query Template
		*/
		exec sp_get_query_template 
				  @querytext = @querytext
				, @templatetext = @templatetext output
				, @parameters = @parameters     output

		/*
			Display Query Template
		*/
		select 
				  [@querytext] = @querytext
				, [@templatetext] = @templatetext
				, [@parameters] = @parameters


		if (@templatetext is null)
		begin


			EXEC [master].DBO.xp_sprintf 
					  @errBuffer OUTPUT
					, @ERR_sp_get_query_templatefailed
					, @querytext


			raiserror(@errBuffer, 16, 1)

		end
		else
		begin

			EXEC sp_create_plan_guide 
						@name = @planGuideName
					,   @stmt = @templatetext
					,   @type = @planType
					,   @module_or_batch = NULL
					,   @params = @parameters
					,   @hints  = @hint
					;

		end

		select 
				  [src] = 'sys.plan_guides for specific plan guide name'
				, tblSPG.*
		from   sys.plan_guides tblSPG
		where  tblSPG.[name] = @planGuideName

		
		select 
				  [src] = 'sys.plan_guides for all'
				, tblSPG.*
		from   sys.plan_guides tblSPG
		

	rollback tran
	go

 

Output

createdWithNameOtherThanSupplied-20160519-1146AM

 

Connect

Relevant Connect Items

  1. sp_create_plan_guide does not properly create plan guides with special characters in plan guide name – by Daniel Adeniji
    • 2724821
    • Opened By : Daniel Adeniji
    • Date Created – 2016.05.19
    • Type :- Bug
    • Status :- Active

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s