IBM Support

Only the first 1024 characters of a nvarchar(max) column are presented in a report based on a DQM package

Technote (troubleshooting)


Problem(Abstract)

A Microsoft SQL Server 2008 table contains an nvarchar(max) column containing 6000 characters. When the column is reported in a List report in Report Studio, it only shows the first 1024 characters.

Cause

The product is behaving as designed. The SQL Server data type nvarchar(max) is mapped to text blob (clob) in DQM. By design, when DQM sees a text blob column, it generates the following SQL for the text blob column...

CAST(SUBSTRING(tab1.my_col2 FROM 1 FOR 1024) AS VARCHAR(1024)) AS my_col2

which explains why, in Report Studio, you only see the first 1024 characters.


Resolving the problem

To obtain the desired results, in the report you can change the expression for my_col2 from...


[db].[tab1].[my_col2]

to...

cast(substring([db].[tab1].[my_col2],1,6000) as varchar(6000))

The following Cognos SQL will be generated...

SELECT
SUM(tab1.my_col1) AS my_col1,
CAST(SUBSTRING(tab1.my_col2 FROM 1 FOR 6000) AS VARCHAR(6000)) AS my_col2
FROM
db_20748_2008.db_20748.dbo.tab1 tab1
GROUP BY
CAST(SUBSTRING(tab1.my_col2 FROM 1 FOR 6000) AS VARCHAR(6000))

and all 6000 characters will be displayed in the report.

Document information

More support for: Cognos Business Intelligence
Report Studio

Software version: 10.1, 10.1.1, 10.2, 10.2.1

Operating system(s): AIX, HP-UX, Linux, Solaris, Windows

Reference #: 1657723

Modified date: 12 September 2014


Translate this page: