Jonathon Moore reported this error which seems to be related to this issue...

"The ntext data type cannot be selected as DISTINCT because it is not comparable."

From:

SELECT DISTINCT r.* FROM mdl_role r, mdl_role_assignments ra, mdl_role_allow_override rao WHERE ra.userid = 2 AND ra.contextid IN (1) AND rao.roleid = ra.roleid AND r.id = rao.allowoverride ORDER BY r.sortorder ASC

• line 686 of lib/dmllib.php: call to debugging()
• line 966 of lib/dmllib.php: call to get_recordset_sql()
• line 4128 of lib/accesslib.php: call to get_records_sql()
• line 65 of admin/roles/assign.php: call to get_overridable_roles()

Is there some quick-fix MS SQL config option that can make this work?

if I'm not wrong, that exact problem was fixed some weeks ago (see MDL-16034) both in 19_STABLE and HEAD, so updating to latest 1.9.x weekly should solve it.

AFAIK there isn't any switch in MS-SQL to change that behaviour.

TEXT and IMAGE (formerly CLOB and BLOB) columns have serious limitations both in DISTINCT and UNION queries. And that not only affects MSSQL but also Oracle (plus causing big speed problems under MySQL and PostgreSQL).

So we should rewrite those queries always, avoiding using them in DISTINCT and UNION clauses (see again MDL-16034 for a general-purpose way to rewrite them, avoiding TEXT columns in those calculations).

We can consider switching to nvarchar(max) under MSQL in the future, yes. But that won't solve the underlying problem, so better to continue rewriting queries for now. IMO.

Ciao

I have Nick loading up the latest weekly to see if it resolves the error messages we have seen so far.

Based on what you are saying, I take it that its not a good idea for us to manually change the data types to nvarchar on this instance of Moodle as a form of work around?

Thanks for the follow up on this. We will report back shortly.