Issue function db.hex - upgrade DB SQL2016

Products

• CA Service Management - Service Desk Manager

Issue/Introduction

In the current 17.3 MDB database, there is a function "dbo.hex" that can be accessed in the SQL Server mdb database, under Programmability -> Functions -> Scalar-valued Functions

This function utilises operators that are deprecated as a result examination of the database via the Microsoft Data Migration Assistant

​Text of the reported deprecation is listed below:

Detected statements that reference removed system stored procedures that are not available in database compatibility level 100 and higher.

Following unsupported system and extended stored procedures cannot be used in database compatibility level 100 and above:
Area: Web assistant

-- sp_makewebtask
-- sp_dropwebtask
-- sp_runwebtask
-- sp_enumcodepages

The "Corrective Action" provides the alternative replacements for some of the unsupported objects that can be used to modify your applications."

Object '[dbo].[hex]' uses stored and extended procedures 'xp_varbintohexstr' that are not supported in database compatibility level 100 and higher. For more details, please see: Line 17, Column 8.

 

Environment

Release : 17.1 and higher

CA Service Desk Manager

Resolution

the fix for this issue will be included in the forthcoming RU22 update for 17.3. 

As the original issue was detected in 17.1, which is an EOS release, there will not be a specific update issued for 17.1 as a backdated patch.  Our best advice for migrating to 17.1 over to 17.3 under these conditions:

- Arrange the 17.1 to 17.3 migration, preserving the backend database as much as possible.  We recommend using the Swing Box migration process.   If you are planning to use the Microsoft Migration Data Assistant to transfer the database, please check for any settings which will transfer the database over "as-is" (no changes or modifications made)

- Once you have your 17.3 GA implementation, apply 17.3 RU22, which will then update the backend database function.