IBM Support

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

Technote (troubleshooting)


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.


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...



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

The following Cognos SQL will be generated...

SUM(tab1.my_col1) AS my_col1,
CAST(SUBSTRING(tab1.my_col2 FROM 1 FOR 6000) AS VARCHAR(6000)) AS my_col2
db_20748_2008.db_20748.dbo.tab1 tab1
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.2, 10.2.1,, 10.2.2

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

Reference #: 1657723

Modified date: 02 December 2013

Translate this page: