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

Can't use = (equal) comparison with Nvarchar and Ntext

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 1.7.1
    • Fix Version/s: 1.7.2, 1.8, 1.9
    • Component/s: Questions
    • Labels:
      None
    • Environment:
      Windows 2K3, IIS6, MSSQL2005,PHP5.12
    • Database:
      Microsoft SQL
    • Affected Branches:
      MOODLE_17_STABLE
    • Fixed Branches:
      MOODLE_17_STABLE, MOODLE_18_STABLE, MOODLE_19_STABLE

      Description

      when upgrading from version 1.7 to 1.7.1 an error occurs during question upgrade for Numerical question. It returns an error that states:

      The data types ntext and varchar are incompatible in the equal to operator.

      I have found the following information in books online, index entry Boolean expressions:

      "Comparison operators test whether two expressions are the same. Comparison operators can be used on all expressions except expressions of the text, ntext, or image data types. The following table lists the Transact-SQL comparison operators."

      = (Equals)
      > (Greater Than)
      < (Less Than)
      >= (Greater Than or Equal To)
      <= (Less Than or Equal To)
      <> (Not Equal To)
      != (Not Equal To)
      !< (Not Less Than)
      !> (Not Greater Than)

      If your description are always (or even usually) under 8000 characters, you need to change to a varchar type. Or you might want to implement two columns, one a smaller description, the other a text column that has long values. Using the current column:

      You can use LIKE or PATINDEX, or cast the values to a varchar (perhaps in a computed column) if you need to do this frequently and not on a really large value:

        Attachments

          Activity

            People

            Assignee:
            timhunt Tim Hunt
            Reporter:
            tommcmur Tom McMurtry
            Tester:
            Nobody
            Participants:
            Component watchers:
            Tim Hunt, Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:
              Fix Release Date:
              30/Mar/07