Details
-
Type:
Bug
-
Status:
Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 1.9.2
-
Fix Version/s: 1.9.3
-
Component/s: Administration
-
Labels:None
-
Environment:Windows 2003 Standard sp2, php 5.2.6, MySQL 5.0.67 (web server)
Windows 2003 Standard sp2, Microsoft SQL 2005 (database server)
-
Database:Microsoft SQL
-
Affected Branches:MOODLE_19_STABLE
-
Fixed Branches:MOODLE_19_STABLE
Description
Assign Roles fails to display created roles when running with Microsoft SQL 2005.
Problem:
Query in class "accesslib::get_assignable_roles()"
SELECT DISTINCT r.*
FROM {$CFG->prefix}role r,
{$CFG->prefix}role_assignments ra,
{$CFG->prefix}role_allow_assign raa
WHERE ra.userid = $USER->id AND ra.contextid IN ($contexts)
AND raa.roleid = ra.roleid AND r.id = raa.allowassign
ORDER BY r.sortorder ASC
Cannot use keyword DISTINCT on Column Description with datatype as ntext. Returns the error as follows:
"Msg 421, Level 16, State 1, Line 1
The ntext data type cannot be selected as DISTINCT because it is not comparable."
FIX:
Use the datatype nvarchar(MAX). ntext is now considered a depricated datatype in MSSQL
Activity
- All
- Comments
- History
- Activity
- Source
- Test Sessions
Hi Ben,
fix committed to 19_STABLE and HEAD.
While the change to nvarchar(MAX) solves the issue under MSSQL, it doesn't under Oracle (obviosuly), so we'll continue needing to "fix" those queries to get cross-db.
Anyway, I agree we should consider migrating to nvarchar(MAX), just have to learn more about it (and potential issues). I wouldn't change DB manually for now. Let's see how it evolves.
Closing this as resolved. Thanks and ciao 
Same thing applied to us on our install. Altering the field to nvarchar(MAX) resolved the issue.