I had just completed the upgrade process from the old attforblock 2012120700 to the latest version of the module for Moodle 2.4, 2.4.3 (2013070404). The upgrade process went smoothly and everything appeared to be working properly. But in further testing, the module would crash when trying to view the detail report for a student, or when student would try to access the module.
With debugging turned in, this was the error detail:
Debug info: The ntext data type cannot be selected as DISTINCT because it is not comparable.
SELECT ats.id, ats.groupid, ats.sessdate, ats.duration, ats.description, al.statusid, al.remarks
FROM mdl_attendance_sessions ats
RIGHT JOIN mdl_attendance_log al
ON ats.id = al.sessionid AND al.studentid = ?
WHERE ats.attendanceid = ? AND ats.sessdate >= ? AND
ats.sessdate >= ? AND ats.sessdate < ?
UNION
SELECT ats.id, ats.groupid, ats.sessdate, ats.duration, ats.description, al.statusid, al.remarks
FROM mdl_attendance_sessions ats
LEFT JOIN mdl_attendance_log al
ON ats.id = al.sessionid AND al.studentid = ?
WHERE ats.attendanceid = ? AND ats.sessdate >= ? AND
ats.sessdate >= ? AND ats.sessdate < ? AND ats.groupid = ?
ORDER BY sessdate ASC
[array (
0 => '3638',
1 => '1',
2 => '1348470000',
3 => 1378018800,
4 => 1380610800,
5 => '3638',
6 => '1',
7 => '1348470000',
8 => 1378018800,
9 => 1380610800,
10 => 0,
)]
Error code: dmlreadexception
Stack trace:
line 426 of \lib\dml\moodle_database.php: dml_read_exception thrown
line 256 of \lib\dml\mssql_native_moodle_database.php: call to moodle_database->query_end()
line 714 of \lib\dml\mssql_native_moodle_database.php: call to mssql_native_moodle_database->query_end()
line 748 of \lib\dml\mssql_native_moodle_database.php: call to mssql_native_moodle_database->get_recordset_sql()
line 1143 of \mod\attendance\locallib.php: call to mssql_native_moodle_database->get_records_sql()
line 375 of \mod\attendance\renderables.php: call to attendance->get_user_filtered_sessions_log_extended()
line 74 of \mod\attendance\view.php: call to attendance_user_data->__construct()
So the issue is with the ntext field attendance_sessions.description being included in the UNION/SELECT DISTINCT, ntext fields are not allowed to be used with these commands. The ntext field type is deprecated in MS SQL Server 2005+ in favor of nvarchar(MAX).
After changing the description field type to nvarchar(MAX), the report page is now working for both teacher and student with existing data. In brief testing of creating a new session and taking attendance, it also worked correctly. Our faculty return next week for our Fall quarter, and I should know then if there are any issues after they use it more extensively.