Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-16034

Assign Roles fails to display roles when installed with Microsoft SQL. Cannot use the ntext datatype when DISTINCT r.* is used in a query. Suggest using nvarchar(MAX) instead.

    XMLWordPrintable

    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

        Attachments

          Activity

            People

            • Votes:
              1 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:
                Fix Release Date:
                15/Oct/08