Xtraction Error System.Data.SqlClient.SqlException Cannot find either column "dbo" or the user-defined function

Products

• SUPPORT AUTOMATION- SERVER
• CA Service Desk Manager - Unified Self Service
• CA Service Desk Manager
• CA Service Management - Asset Portfolio Management
• CA Service Management - Service Desk Manager

Issue/Introduction

When trying to use some Xtraction fields such as:

 

"Related - Children#"

under DATA SOURCE CA Service Desk\Incidents

<Please see attached file for image>

 

"Related - Children#", "Related - Incidents#", "Related - Problems#" or "Related - Request#"

under DATA SOURCE CA Service Desk\Change orders

<Please see attached file for image>

 

You might see the error:

<Please see attached file for image>

 

In addition, Xtraction logs would present similar error messages:

 

System.Data.SqlClient.SqlException (0x80131904): Cannot find either column "dbo" or the user-defined function or aggregate "dbo.xtraction_req_get_children", or the name is ambiguous. 

 

System.Data.SqlClient.SqlException (0x80131904): Cannot find either column "dbo" or the user-defined function or aggregate "dbo.xtraction_chg_get_children", or the name is ambiguous. 

 

 

Environment

Service Desk Manager - ANY VERSION Xtraction - ANY VERSION

Cause

There are missing user-defined functions in the system that need to be loaded by running a SQL script provided by Xtraction.

 

 

 

 

 

Resolution

In order for certain fields in various SDM views to work, a number of User Defined Functions need to be added to the CA SDM MDB database.

 

a. Execute the appropriate script to create the functions (either R12_5_ORACLE_UPDATES.sql or R12_5_SQLSERVER_UPDATES.sql) against the MDB database;

 

b. If using MS SQL Server, edit the R12_5_SQLSERVER_PERMISSIONS.sql script to reference the correct user account used by Xtraction and then execute it against the MDB database

 

Please download these scripts from here..

Additional Information

It is always advisable to perform database backup before making modifications on it.

For any additional doubts or concerns on the application of these scripts, please raise a new case at Support.