"ODBC OpenAccess SDK driver - Invalid date literal" error when running CA Business Intelligence (CABI) reports

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

Issue

 

When you try to run a CA Business Intelligence (CABI) report, may receive a small window prompt stating the following:

 

"A database error occurred. The database error text is: [DataDirect][ODBC OpenAccess SDK driver][OpenAccess SDK SQL Engine]

Invalid date literal N'XXX-XX-XX' - must be in form {d'yyyy-mm-dd hh:mm:ss xm} at N'XXXX-XX-XX ...} (WIS 10901)

 

Environment

 

CA Service Desk Manager 14.x

CA Service Desk Manager 12.x

CA Business Intelligence 3.x

 

Cause

 

When date range values differ from the ones outlined in an Out of the Box CABI installation, it will cause date range filters to work incorrectly and ultimately cause this error.

 

 

Resolution

 

To resolve this issue, you must configure the date range values and join parameters on the server that CABI has been installed:

 

1. Navigate to: 
   Program Files\CA\SC\CommonReporting3\BusinessObjects Enterprise 12.0\win32_x86\dataAccess\connectionServer\odbc\
2.    Open the ODBC.PRM file using a text editor
3.    Navigate to the <Configuration> section of the file, and locate the following line:                                                                                                  <Parameter Name="USER_INPUT_DATE_FORMAT">{\d 'yyyy-mm-dd'}</Parameter>
4.    Modify this line to include " HH:mm:ss am/pm".  So the line should now look like this:                                                                                          <Parameter Name="USER_INPUT_DATE_FORMAT">{\d 'yyyy-mm-dd HH:mm:ss am/pm'}</Parameter>
5.    Locate the following line to configure the join parameters: <Parameter Name="EXT_JOIN">NO </Parameter>
6.    Modify this line to replace NO with YES: <Parameter Name="EXT_JOIN">YES</Parameter>
7.    Locate the following line: <Parameter Name="OUTERJOINS_GENERATION">NO</Parameter>
8.    Modify this line to replace NO with FULL_ODBC: <Parameter Name="OUTERJOINS_GENERATION">FULL_ODBC</Parameter>
9.    Add the following lines AFTER the OUTERJOINS_GENERATION parameter line:                                                                                                    <Parameter Name="LEFT_OUTER"></Parameter>                                                                                                                                            <Parameter Name="RIGHT_OUTER"></Parameter>                                                                                                                                        <Parameter Name="OUTERJOINS_COMPLEX">Y</Parameter>
10.    Save this ODBC.PRM file
11.    Restart the Server Intelligence Agent and Tomcat services, located in the Central Configuration Manager.
12.    After the restart has been successful, the changes will take effect, and the date range/join parameters will be properly configured, causing the initial error to no longer occur. 

 

Additional Information

 

This information can be found on Page 152 of the CA SDM 12.7 Implementation guide

 

This information can be found on Page 235 of the CA SDM 12.9 Implementation guide

 

Environment

Release: SDMU0M99000-14.1-Service Desk Manager-Full License
Component: