conflict analysis error due to data partition

Products

• CA Service Management - Service Desk Manager
• CA Service Desk Manager

Issue/Introduction

Issue:
======

Conflict Analysis is giving below error "chg::find_new_schedule_c(895) - Error 1: process_conflicts_0(896) - Error: 'AHD04199:An unexpected Database error occurred. Contact your administrator." if data partition is set as: (affected_contact = @root.id and category.sym not like '%Add%') or category.sym like '%Move%'

Environmental Details:
==================

USRD 17.2 RU 11/RU12 /Windows (issue also seen in 17.3)

Steps:
======

1. Open Data Partition "Service Desk Analyst" for editing

2. Create a new DP Constraint.
    Type: View
    Table: Change_Request
    Constraint: (affected_contact = @root.id and category.sym not like '%Add%') or category.sym like '%Move%' 

3. Change Role to one that uses the above Data Partition (Role:  Level 2 Analyst has this DP by default).  Create few CO's with the following conditions.

- Each one has the same CI's listed: test. 

- Make sure each CO has the same exact value for "Scheduled Start Date" and "Scheduled Duration". 

- Name one of the "Move" entries as the category. 

- Name the given login being accessed as the affected user.

4. From any of the CO's, click on "View Scheduler".

  ​

5. The Change Conflict will be created

6. From any of the given CO's, click on Additional Information Tab, then Conflicts tab -> Conflict Analysis and below error will be displayed

​

 

7. Without the data partition the error is not seen.

The stdlogs will also mention:

07/08 18:05:51.57 SERVER1  domsrvr             10248 ERROR        sqlquery.c            5557 Can't add join for Change_Request.category = Change_Category.code because both sides exist in the data partition ( Change_Request.affected_contact = U'3E6E78A6F53DF546A77D568BA9D0F587' and Change_Request.category = Change_Category.code AND Change_Category.sym NOT LIKE '%Add%' ) or Change_Request.category = Change_Category.code AND Change_Category.sym LIKE '%Move%' 

07/08 18:07:42.76 SERVER1  sqlagt:select23     10120 ERROR        sqlclass.c            1013 SQL Execute  failed: [Microsoft SQL Server Native Client 11.0] [ SQL Code=1013 SQL State=42000] The objects "chgcat" and "chgcat" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

07/08 18:07:42.77 SERVER1  sqlagt:select23     10120 ERROR        sqlclass.c            1014 Clause (SELECT C.id, A.nr, B.id, C.sched_start_date, C.sched_end_date,  <OMITTED>

07/08 18:07:42.77 SERVER1  bpvirtdb_srvr        5028 ERROR        vdbmisc.c              688 Miscellaneous Database error occured :[Microsoft SQL Server Native Client 11.0] [ SQL Code=1013 SQL State=42000] The objects "chgcat" and "chgcat" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

07/08 18:07:42.77 SERVER1  spelsrvr             7892 ERROR        chg.spl               6924 process_conflicts_0(1015) - Error: 'AHD04199:An unexpected Database error occurred. Contact your administrator.'

Environment

Release : 17.2 (up to RU16) and 17.3

Component : SERVICE DESK MANAGER

Resolution

This is a known defect and is currently under investigation by Engineering team with a test fix available.  Kindly raise a case with support to request the necessary test fix.