Patch Management Solution is failing to upgrade: 'TRY_CONVERT' is not a recognized built-in function name.
Article ID: 211591
Updated On:
Products
Issue/Introduction
In this particular scenario, the customer was upgrading from ITMS 8.5 RU4 to 8.6. During the configuration process, SIM failed with the following error while configuring Patch Management:
Entry 1:
Configuring product: C:\Program Files\Altiris\Patch Management\Core\Config\PatchManagementCore.config
-----------------------------------------------------------------------------------------------------
Date: 3/26/2021 3:41:43 PM, Tick Count: 7323046 (02:02:03.0460000), Size: 375 B
Process: AeXSvc (5196), Thread ID: 31, Module: Altiris.NS.dll
Priority: 4, Source: Altiris.NS.Installation.ProductConfigurationWorker.ConfigureProductInternal
Entry 2:
Failed to install product.
'TRY_CONVERT' is not a recognized built-in function name.
[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& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Altiris.NS.DataAccessLayer.DatabaseAbilities.ExecuteNonQuery(String sql)
at Altiris.NS.ContextManagement.AdminDatabaseContext.<>c__DisplayClass26_0`1.<PerformTransactedDeadlockRetry>b__0(IDatabaseContext ctx)
at Altiris.Database.DatabaseContext`1.RetryActionRequest.Perform(Boolean throwIfRetriedOut)
at Altiris.Database.DatabaseContext`1.PerformWithDeadlockRetryHelper(Int32 retries, Int32 retryDelay, Boolean outerTransaction, Getter`1 getContext, Action`1 action, Action`1 retry, Boolean selfTransaction, String deadlockMessage, String category)
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(XmlNode installationNode)
at Altiris.PatchManagementCore.PatchManagementBaseInstallation.OnInstallProduct(XmlNode installationNode)
at Altiris.NS.StandardItems.Product.ProductInstallation.InstallProduct()
SQL Exception details: code=195, line=34, procedure=spPMCoreReport_AllSoftwareBulletins
Exception logged from:
at Altiris.NS.StandardItems.Product.ProductInstallation.InstallProduct()
at Altiris.PatchManagementCore.PatchManagementCoreInstallation.InstallProduct()
at Altiris.NS.Installation.ProductConfigurationWorker.ConfigureProductInternal(String, Boolean, Altiris.NS.Serialization.SerializationMode)
at Altiris.NS.Installation.ProductConfigurationWorker.ConfigureProduct()
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: 3/26/2021 3:41:45 PM, Tick Count: 7324671 (02:02:04.6710000), Size: 3.74 KB
Process: AeXSvc (5196), Thread ID: 31, Module: Altiris.NS.StandardItems.dll
Priority: 1, Source: Altiris.NS.StandardItems.Product.ProductInstallation.InstallProduct
Environment
ITMS 8.5, 8.6
Cause
We have the following statement in upgrade documentation
SQL Server had an old compatibility mode set.
See the following reference online (https://social.msdn.microsoft.com/Forums/officeapps/en-US/a347ab0a-ef2e-464a-a914-9a5c2cd7d08e/tryconvert-is-not-a-recognized-builtin-function-name?forum=transactsql):
Resolution
The error suggests the database compatibility level is less that SQL 2012. You'll need to change the compatibility level to 110 (SQL 2012) or 120 (SQL 2014) in order to use TRY_CONVERT.
Specify the database compatibility level to match the SQL instance version unless you have a reason to do otherwise.
ALTERDATABASE YourDatabase SET COMPATIBILITY_LEVEL =120;
In his case, his database was set to 2008 compatibility mode:
Since it was a SQL Server 2012, we changed to the proper level (110).
After making the change, run a repair on Patch Management Solution or run a Full Repair on all the installed Suite.
Feedback
