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

Document ID : KB000005436
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

When trying to use some Xtraction fields such as:

 

"Related - Children#"

under DATA SOURCE CA Service Desk\Incidents

TEC1311044-1.png

 

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

under DATA SOURCE CA Service Desk\Change orders

TEC1311044-3.png

 

You might see the error:

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. 

 

 

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.

File Attachments:
TEC1311044.zip