I am trying to link Moodle course enrollment with the class rosters in our school's student management system (SMS). I'm trying to do it in a way where multiple classes in the SMS can be grouped into one course in Moodle. Our classes in the SMS are named with the format "MoodleCourseID (Term) OtherIdentification". The SQL for the view in the SMS database (Microsoft SQL) that Moodle is linked to uses string functions to crop everything but the MoodleCourseID from the class name. For example:
Original SMS class name: "Algebra 8 (S1) 9th Grade" or "Algebra 8 (Q2) Section 3"
In the view of the SMS database these both become: "TCH_Algebra 8"
The Moodle course idnumber to link to: "TCH_Algebra 8"
Users are linked to the username field of the user table in Moodle.
Does anyone have ideas on why this isn't working? I can't seem to get the databases to link at all, so I don't know if it's related to trying to make it work in this way or not. All the other settings appear to be right (server name, database name, username, password, table name, etc.) I can go into more detail on this if anyone needs more information. I have attached the SQL query that generates the view that Moodle is supposed to be linking to.