Crystal reports not remapping nvarchar fields that have been resized to nvarchar(max)

Question

I have a Crystal Report which is connected to a SQL Server database via the SQLOLEDB provider.

Some of the columns referenced in the report have been resized in the underlying SQL Server database from a set nvarchar size to nvarchar(max).

On verifying the report, Crystal Reports 2008 SP3 can't see the column to remap it and the column no longer appears in the list of columns in the table available for mapping.

I've also tested this in Visual Studio 2013 using Crystal Reports 13_0_10 and the same behaviour is observed.

The only solution that I can see is to note the physical location on the report, verify the database (which removes the column) then re-adding the column to the report in the same location (which will prove very time consuming).

Is there an easier way to accomplish this task? I'd have expected newer versions of Crystal to be able to handle this.

Answer 1

Add to your ConnectionString:

DataTypeCompatibility=80

SQL Server 2005 added the new varchar(max) data type, and i think the ADO library Crystal is using is confused. Adding DataTypeCompatiblity=80 will cause SQL Server to return the columns as "ntext" rather than "nvarchar(max)".