Skip to main content
Planview Customer Success Center

Error in Cognos report - Conversion failed when converting from a character string to uniqueidentifier

SYMPTOMS
A Cognos Report fails with a server error message.

ERROR
RQP-DEF-0177 An error occurred while performing operation 'sqlOpenResult' status='-28'. DP-ERR-2082 An eror has occured. Please contat your administrator. The complete error has been logged by CAF.

Upon checking the cogserver.log file, there is a more detailed error message:

ERROR
UDA-SQL-0115 Inappropriate SQL request.  UDA-SQL-0564 [Microsoft SQL Server Native Client 11.0]Conversion failed when converting from a character string to uniqueidentifier. (SQLSTATE=42000, SQLERRORCODE=8169)


REASON
The report contains a query that is the result of a UNION operator. One of the columns of the union is of data type uniqueidentifier in the first source and of data type string in the second source of the union. The column in the second source is in fact a constant expression '' (= empty string) to indicate that there is no value. This causes the conversion error as '' cannot be converted to uniqueidentifier.

RESOLUTION
To indicate that there is no value, a NULL value should be returned. In the current case, null was not accepted by Cognos. So instead an expression was put in that always evaluates to null:

CASE WHEN 1>3 THEN '' ELSE null END

That was the column in the second source of the union returns always null and when performing the union-operator, it can be correctly converted to uniqueidentifier.

NOTE
There can be other reasons for this error messages, please check any operation where two columns are involved, like joins, unions, intersections, filters,...