A query with Distinct against a SQL view with Distinct truncates string (1 Viewer)

Just wanted to know if anyone had heard of this or woul know why it does this.

A SQL View - has Distinct - one of the fields is a large text field.
This works fine. The Access DSN-Less linked table shows all the charaters in the text field.

On the Access side, a query against the the Linked Table had a Select Distinct *
The recordset assigned the result into a string variable.
It should have been over 530 characters - it was one word.
Removing the last two words, it then was about 212 characters (the last half of the complete field).
All the records with about 212 characters (or less) appeared to work fine.

On the Access query, removing the Distinct (just Select *) fixed the problem.

Granted, the 2nd Distinct was not required.
This just seemed like a crazy result. It wasn't as if "oh, the 2nd Distinct is causing this to happen" moment arrived.

Anyone heard of this?

DSN-Less ODBC connections using SQL Server Native Client 11.0 and DAO recordsets.

Error replicated on SQL Server 2005 R2 using ODBC DSN. Single record in table.

I get the last three words from 260 characters in the field.

Same error if the Distinct query is used against the linked table rather than a view with distinct.

Doesn't happen with Group By which would give the same result on records with no duplicates.

Did some extra reading. It claims that even with just MS Access query using Distinct then run a query using Distinct again: It does truncate. Something about the processor taking too much iteration.
Partly due to a memo field use to not be searchable. Now, it is searchable in 512 (or something like that) blocks. So, the Distinct probably (just guessing here) only compares the last blocks of text, not the entire text.

So, I am going to add this to my "Check for this" list.
Really, there is no normal cause to run a Distinct on a Distinct.

My preference is to run a Distinct on the SQL Server View side. Although, I can't totally justify saying "always".
Still, if the SQL Server Views use Distinct, the Access client programmer should be aware. If not aware, they should test situations where long strings are returned. It would be easy to look at a column and say "hey, we have lots of text" instead of saying "hey, we have all of the text".

Will write this up to "As Designed".
Guess I learned something new.