Knowledgebase
Home » Item #423
Error after upsizing Access to MS SQL: "The text, ntext, or image data type cannot be selected as DISTINCT"
Question
I have upsized my Access database to MS SQL. However, I now get this error message on many pages:
The text, ntext, or image data type cannot be selected as DISTINCT
Why is this and how can I fix it?
Answer
Fields that contain more that 255 characters of text in Access must be 'memo' fields.
However, the upsizing wizard turns 'memo' fields into type 'ntext' or 'text' in MS SQL (not to be confused with the 'text' data type in Access, which is more analogous to MS SQL's 'varchar' or 'nvarchar' data types).
In many queries, these MS SQL 'text' field types will cause errors in DISTINCT statements. Therefore, they must be changed into type 'varchar' (max 8000 chars) or 'nvarchar' (max 4000 chars). Ensure you set the length of these fields long enough for the text you wish to place into them.
Once all 'ntext' and 'text' fields have been changed to 'nvarchar' and 'varchar', the errors should disappear.