Internal Server Error URL: http://servername/fcm.web/api/consolidation/history/job STATUS: 500 ... "Exception occured on data source connection ... "(0x80040E07): The conversion of a varchar data type" using Controller Web

Troubleshooting

Problem

User launches Controller Web. User successfully chooses a database, and logs on.

User selects one of the projects. An error appears.

If the user clicks OK to the error, then they can proceed. However, as they use Controller Web (opening other objects/menus) similar errors appear.

Symptom

Screen:

Internal Server Error

URL: http://servername:9081/fcm.web/api/consolidation/history/job STATUS: 500

Unexpected exception

Log entry: server=<sqlservername>, NDC=ADM@<databasename>, Time=19-06-18 15:35.13

For more details, please use the button below.

Show details

Failed URL: /fcm.web/api/consolidation/history/jobException: Unexpected exception

Log file ("com.ibm.cognos.fcm.log") on the Controller application server:

2019-07-17 17:01:46 | | ERROR | [com.ibm.cognos.fcm.wmc.ccrws.system.SystemPollerDaemon] PollingDaemon[status-updater-client]-55 encountered a problem. Running again after 30000 ms.

com.ibm.cognos.fcm.wmc.ccrws.ServerEndpointException: Cannot retrieve any status updates: Exception occured on data source connection: D:\Program Files\ibm\cognos\ccr_64\Data\databasename.Udl with message: System.Data.OleDb.OleDbException (0x80040E07): The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)

at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)

at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)

at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)

at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)

at ControllerServerCommon.Utility.ReadFromDataBase(String udlPath, String selectStatement, Int32 commandTimeout)

at FrStatusB.RealTimeStatusUpdatesWeb.GetReportingAndFormStatusData(String udlPath, WebDataSource dataSourceItem, List`1 contextList, String[] companyCodes, DateTime timeStampDate)

at FrStatusB.RealTimeStatusUpdatesWeb.GetStatusUpdatesList(String udlPath, WebDataSource dataSourceItem, List`1 contextList, DateTime timeStampDate)

at FrStatusB.RealTimeStatusUpdatesWeb.GetResponseStatusUpdatesWebContentItem(String udlPath, WebDataSource dataSourceItem, List`1 contextList, DateTime timeStampDate)

at FrStatusB.RealTimeStatusUpdatesWeb._Closure$__4-0._Lambda$__0(String udlPath)

Cause

Invalid/incorrect language settings relating to the:

Controller application server
UDL file
SQL database server.

Example #1:

In one real-life customer environment:

Controller application server language is set to: English (United Kingdom)
SQL Server language setting: English (United States)

In this scenario, the problem was caused by the UDL file properties setting 'Current Language' being blank/empty.

This means that the Operating System language (of the Controller application server) was used.

Example #2:

In a different real-life customer environment:

Controller application server regional settings set to: Finnish
SQL Server language setting: English (United States)

Environment

This problem is very rare (affects very few customers).

Diagnosing The Problem

To check what the default language (of the Microsoft SQL server) is, perform the following steps:

1. Launch SQL Management Studio

2. Right-click on the relevant server, and choose 'properties'

3. Click: Advanced

4. Look at the value for: Default Lanaguage

Resolving The Problem

The solution will vary depending on environment.

Example #1

Modify the UDL file properties, to change the setting of 'Current Language' to be the same as the SQL database server.

Steps:

In one real-life customer example, the solution was to:

1. Browse to the location of the UDL file

TIP: By default, this is: C:\Program Files\ibm\cognos\ccr_64\Data

2. Right-click on the relevant UDL file, and choose 'properties'

3. Click tab 'All'

4. Modify the value of 'Current Language' to be the correct value

For example: english

5. Save changes

6. Test.

Example #2

Modify the regional settings (specifically, the system locale) of the Controller application server, so that it is compatible with the SQL server.

Steps:

In one real-life customer example, the solution was to change the settings from 'Finnish' to 'English (United States)' using the instructions inside separate IBM Technote #253767.

===========================================

More Information:

Microsoft SQL server supports many different SQL language codes, for example:

english = English (United States)
british english = English (United Kingdom)

TIP: To find out what values are supported by your SQL server, run this SQL query: EXEC sp_helplanguage;

For example, SQL 2017 supports the following:

===========================================

Related Information

253767 - cc.fExpVal fails when the Controller application server's Regional Set…

Document Location

Worldwide

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SS9S6B","label":"IBM Cognos Controller"},"Component":"","Platform":[{"code":"PF033","label":"Windows"}],"Version":"10.4.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Tips