The ntext data type cannot be selected as DISTINCT

[MisterY]

Trying to export HTML documentation in 8.0 beta 1 pops up the following error message:

Microsoft OLE DB Provider for SQL Server
The ntext data type cannot be selected as DISTINCT because it is not comparable.

Any idea why this is happening?

[MisterY]

Just removed 8.0 and installed latest 7.5.850. Same repository, same package, same options - and export works fine.

Regards

[Paolo F Cantoni]

Trying to export HTML documentation in 8.0 beta 1 pops up the following error message:

Microsoft OLE DB Provider for SQL Server
The ntext data type cannot be selected as DISTINCT because it is not comparable.

Any idea why this is happening?

HTML generation works OK for us in v8.  But we have a "modified" SQL Server DB.  We standardised all ntext columns to nvarchar(MAX).

The impact of that is that (at least theoretically) so long as the amount of text in the column (typically Notes fields) is less than the -b large_object_bytes parameter (which defaults to - I think - 8K bytes) it is comparable.

But the real issue is that EA appears to be trying to SELECT DISTINCT on something that (by definition) is non comparable.  As a Data Architect, I wouldn't be designing ntext and  nvarchar(MAX) as comparable - notwithstanding SQL Server's ability to make the latter so...

Sounds like a typo bug has crept in...

Have you formally reported the problem?

Paolo

[MisterY]

Hey, Paolo,

Thanks for these comments. Yep, I've reported the bug through the 8.0 bug report channel.
I just noticed this when I needed diagrams exported as images.

Funny thing is that there should be no differences in the existing functionality and HTML export is, presumably, one of those.

Cheers,

Alen

[Geert Bellekens]

Alen,

I think I know why this is happening.
In the new SQL server schema (or the upgrade patch) a number of ntext fields converted into varchar fields.
This is presumably to allow the new indexes and stuff.

There are basically two options to use the new schema (wich boosts performance because of all the indexes).

1. use the upgrade patch provided on the website (attention, doesn't work for SQL2000). There were a few bug in that script, but Sparx support assured me that they are fixed now. I haven't tested the new update script myself though.
Just be carefull to apply the patch, there is no transactional support, so if it fails, it has done half of the work.

2. use the sql create script to create a new database and then use te project transfer to transfer the contents of your model to the new database. This is the option I choose to upgrade our models.

Geert

[Paolo F Cantoni]

Hi Geert,

Looks like you hit the nail on the head...

Sparx have REDUCED the (potential) size of some of those fields (from text or ntext to less than nvarchar(max)).

I haven't upgraded the our DBs to use the new specifications yet but will do shortly - assuming Sparx don't vary the script or the SELECT DISTINCT.

Paolo