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

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


Article ID: 36145


Updated On:


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




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)
CA Service Desk Manager 14.x
CA Service Desk Manager 12.x
CA Business Intelligence 3.x



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.




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


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