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

book

Article ID: 5436

calendar_today

Updated On:

Products

SUPPORT AUTOMATION- SERVER CA Service Desk Manager - Unified Self Service KNOWLEDGE TOOLS 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>

TEC1311044-1.png

 

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

under DATA SOURCE CA Service Desk\Change orders

<Please see attached file for image>

TEC1311044-3.png

 

You might see the error:

<Please see attached file for image>

TEC1311044-2.png

 

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. 

 

 

Cause

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

 

 

 

 

 

Environment

Service Desk Manager - ANY VERSION Xtraction - ANY VERSION

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.

Attachments

1558707279870000005436_sktwi1f5rjvs16qmv.png get_app
1558707278150000005436_sktwi1f5rjvs16qmu.png get_app
1558707276041000005436_sktwi1f5rjvs16qmt.png get_app
1558533930504TEC1311044.zip get_app