Troubleshooting
Problem
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.
Was this topic helpful?
Document Information
Modified date:
15 June 2018
UID
swg21657723