I have been struggling with this issue for some time and the only way around it is to manually write and execute a query that performs what Totara is trying to do.

In my Totara site there is a program that was assigned to 75,000 users. Once the program assignment was removed the scheduled task "totara_program\task\user_assignments_task" attempts to remove these 75,000 program user assignments. The query Totara is trying to execute on my SQL Server exceeds some "expression services limit". The query is 444,538 characters long. I will post the full error below.

SQL Query: SELECT userid FROM mdl_user_enrolments WHERE userid IN ( [SET OF 75,000 USERIDS] ) AND enrolid = '9999' AND status = '1'

There are a some ideas that I have come up with that could solve this issue:

1. Put the userids into a temp table and join on that table which would effectively remove the IN condition that is causing this issue.
2. Have the code execute in batches that restrict the amount of items in the IN condition.
3. Use a different SQL Driver. I am currently using ODBC Driver 11 for SQL Server.
4. Increase the "expression services limit" that is being exceeded. Note: this is not really a viable option as this limit exists to prevent an out-of-memory exception. I just wanted to put it in this list in case someone thinks this could be a potential fix.

Thanks for any assistance resolving this issue!

SQL Server 13.0.5026.0
PHP v7.2.5
php_sqlsrv_72_nts_x64
Totara v11.2 (Build: 20180419.00)
$CFG->dbtype = 'sqlsrv'
$CFG->dblibrary = 'native'

ERROR:

Execute scheduled task: Program user assignments (totara_program\task\user_assignments_task)

... used 2221169 dbqueries

... used 4064.268458128 seconds

Scheduled task failed: Program user assignments (totara_program\task\user_assignments_task),Error reading from database

Debug info:

SQLState: 42000

Error Code: 8632

Message: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.

Backtrace:

* line 312 of \lib\dml\sqlsrv_native_moodle_database.php: call to moodle_database->query_end()

* line 426 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->query_end()

* line 891 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->do_query()

* line 967 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->get_recordset_sql()

* line 1733 of \lib\dml\moodle_database.php: call to sqlsrv_native_moodle_database->get_fieldset_sql()

* line 189 of \enrol\totara_program\lib.php: call to moodle_database->get_fieldset_select()

* line 944 of \totara\program\program.class.php: call to enrol_totara_program_plugin->process_program_reassignments()

* line 671 of \totara\program\program.class.php: call to program->assign_learners_bulk()

* line 60 of \totara\program\classes\task\user_assignments_task.php: call to program->update_learner_assignments()

* line 146 of \admin\tool\task\cli\schedule_task.php: call to totara_program\task\user_assignments_task->execute()