Unable to upgrade due to a failure while dropping a stored procedure because it is referenced by plan guide

Products

• IT Management Suite

Issue/Introduction

During an upgrade from 8.1 RU6 to RU7, a customer had a configuration failure under NS_Hotfix.config complaninig about:

Cannot drop procedure 'dbo.spGetLocalisedItemStrings' because it is referenced
by plan guide 'PlanGuide-spGetLocalisedItemStrings'. Use sp_control_plan_guide
to drop the plan guide first. Record the plan guide definition for future use if
needed.

Failed to configure: C:\Program Files\Altiris\Notification Server\Config\NS_Hotfix.config

Database context invalidated by inner exception
   [Altiris.Database.InvalidDatabaseContextException @ Altiris.Database]
   at Altiris.Database.DatabaseContext`1.ProcessException(Exception exception)
   at Altiris.NS.DataAccessLayer.DatabaseAbilities.ExecuteNonQuery(String sql)
   at Altiris.NS.ContextManagement.AdminDatabaseContext.<>c__DisplayClass9`1.
<PerformTransactedDeadlockRetry>b__8(IDatabaseContext ctx)
   at Altiris.Database.DatabaseContext`1.RetryAction(Int32 retries, Boolean transacted, Getter`1 getContext, Action`1 action, Action`1 retry,
DeadlockRetryArgs& re, Exception& exception, Boolean inTransaction)
   at Altiris.Database.DatabaseContext`1.PerformWithDeadlockRetryHelper(Int32 retries, Boolean inTransaction, Getter`1 getContext, Action`1 action, Action`1
retry, Boolean transacted)
   at Altiris.Database.DatabaseContext`1.PerformTransactedDeadlockRetry(Int32 retries, Getter`1 getContext, Action`1 action, Action`1 retry)
   at Altiris.NS.ItemManagement.ItemHelper.ImportSqlNodes(XmlNodeList nodes, Boolean installing, Boolean resetConnection)
   at Altiris.NS.Installation.Bulk.Installers.Core.CoreInstaller.InstallSqlNodes(XmlNodeList nodes, Boolean useBulkInstaller)
   at Altiris.NS.Installation.Bulk.Installers.Core.CoreInstaller.InstallSqlNodes(XmlNodeList sqlNodes)
   at Altiris.NS.StandardItems.Product.ProductInstallation.Execute(String taskKey, String description, XmlNode node, String xPath, Action`1 actus)
   at Altiris.NS.StandardItems.Product.ProductInstallation.InstallProduct_Database (XmlNode installationNode)
   at Altiris.NS.StandardItems.Product.ProductInstallation.OnInstallProduct(XmlNodeinstallationNode)
   at Altiris.NS.StandardItems.Product.ProductInstallation.InstallProduct()
   at Altiris.NS.Installation.ProductConfigurationWorker.ConfigureProductInternal(String configFile, Boolean ownsProgressContext, SerializationMode serializationMode)
   at Altiris.NS.Installation.ProductConfigurationWorker.ConfigureProductBatchInternal()

Cannot drop procedure 'dbo.spGetLocalisedItemStrings' because it is referenced by plan guide 'PlanGuide-spGetLocalisedItemStrings'. Use sp_control_plan_guide to drop the plan guide first. Record the plan guide definition for future use if needed.
   [System.Data.SqlClient.SqlException @ .Net SqlClient Data Provider]
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Altiris.NS.DataAccessLayer.DatabaseAbilities.ExecuteNonQuery(String sql)

SQL Exception details: code=10513, line=2

Exception logged from:
   at Altiris.NS.Installation.ProductConfigurationWorker.ConfigureProductBatchInternal()
   at Altiris.NS.Installation.ProductConfigurationWorker.ConfigureProductBatch()
   at System.Threading.ExecutionContext.RunInternal(System.Threading.ExecutionContext, System.Threading.ContextCallback, Object, Boolean)
   at System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext, System.Threading.ContextCallback, Object, Boolean)
   at System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext, System.Threading.ContextCallback, Object)
   at System.Threading.ThreadHelper.ThreadStart()

-----------------------------------------------------------------------------------------------------
Date: 8/28/2018 8:41:38 AM, Tick Count: 895336 (00:14:55.3360000), Size: 4.32 KB
Process: AeXSvc (5952), Thread ID: 27, Module: Altiris.NS.dll
Priority: 1, Source:
Altiris.NS.Installation.ProductConfigurationWorker.ConfigureProductBatchInternal

Cause

Invalid custom "Plan Guide" added to the Symantec_CMDB database

Environment

ITMS 8.1 RU7 (during an upgrade from RU6 to RU7)

Resolution

This "Plan Guide" needs to be removed.

According to this link:
https://docs.microsoft.com/en-us/sql/relational-databases/performance/delete-a-plan-guide?view=sql-server-2017

To delete all plan guides in a database:

1. In Object Explorer, connect to an instance of Database Engine.
2. On the Standard bar, click New Query.
3. Copy and paste the following example into the query window and click Execute.

USE Symantec_CMDB; 
GO 
EXEC sp_control_plan_guide N'DROP ALL'; 
GO