Uploaded image for project: 'Plugins'
  1. Plugins
  2. CONTRIB-8408

Upgrade database SQL statements fail for SQL Server

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 3.9.4
    • Fix Version/s: None
    • Component/s: Module: Questionnaire
    • Labels:
      None
    • Plugin Version:
      Release 3.9.1 (Build - 2020111801)
    • Affected Branches:
      MOODLE_39_STABLE

      Description

      I was trying to install the latest Questionnaire 3.9.1 (Build - 2020111801) on Moodle. I am using MS SQL Server 2016. It seems the DML is not the right syntax for SQL Server.

      Plugins check

      This page displays plugins that may require your attention during the upgrade, such as new plugins to be installed, plugins to be upgraded, missing plugins etc. Additional plugins are displayed if there is an available update for them. It is recommended that you check whether there are more recent versions of plugins available and update their source code before continuing with this Moodle upgrade.

      Plugins requiring attention

      Plugins requiring attention 1
      All plugins| 425

      Plugin name / Directory Current version New version Requires Source / Status
      Activity modules
      Questionnaire
      /mod/questionnaire
      2017111103 2020062302
      • Moodle 2019052000
      Additional To be upgraded

       

      Error is:

      Upgrading to new version

      mod_questionnaire

      Error writing to database

      More information about this error
      Debug info: SQLState: 42000<br>
      Error Code: 102<br>
      Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near 'qr'.<br>

      UPDATE mdl_questionnaire_response qr INNER JOIN mdl_questionnaire_attempts qa ON qr.id = qa.rid SET qr.questionnaireid = qa.qid
      [array (
      )]
      Error code: dmlwriteexception
      Stack trace: * line 489 of \lib\dml\moodle_database.php: dml_write_exception thrown

      • line 324 of \lib\dml\sqlsrv_native_moodle_database.php: call to moodle_database->query_end()
      • line 431 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->query_end()
      • line 820 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->do_query()
      • line 730 of \mod\questionnaire\db\upgrade.php: call to sqlsrv_native_moodle_database->execute()
      • line 866 of \lib\upgradelib.php: call to xmldb_questionnaire_upgrade()
      • line 565 of \lib\upgradelib.php: call to upgrade_plugins_modules()
      • line 1917 of \lib\upgradelib.php: call to upgrade_plugins()
      • line 713 of \admin\index.php: call to upgrade_noncore()
         

       

      I believe the correct T-SQL syntax is :

      UPDATE qr SET qr.questionnaireid = qa.qid FROM mdl_questionnaire_response qr INNER JOIN mdl_questionnaire_attempts qa ON qr.id = qa.rid;

        Attachments

          Activity

            People

            Assignee:
            mchurch Mike Churchward
            Reporter:
            r-oberto R-oberto
            Participants:
            Component watchers:
            Mike Churchward
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated: