Custom SQL Commands: Difference between revisions
No edit summary |
|||
| (10 intermediate revisions by 4 users not shown) | |||
| Line 1: | Line 1: | ||
Custom SQL Commands | Custom SQL Commands is a feature for executing customized SQL at specific times in various background jobs. The time depends on the job you attach the SQL command to. | ||
< | This feature is found at: Configurator -> Settings -> Custom SQL Commands | ||
[[File:Custom SQL commands.jpg|none|thumb|556x556px]] | |||
==Program== | |||
The list of UMS programs that supports Custom SQL commands. The list is only filed with programs that you have a license for. | |||
===Show commands after addon file has been run=== | |||
Some of the programs have a custom file that it can run instead of the one supplied by UMS. | |||
If you do not have a custom file then ignore this chekbox | |||
===Show commands after <Program name> has run=== | |||
This allows you to run custom SQL commands after the specific program has finished running. This could be a program like '''SLI'''. | |||
If this is not checked the custom SQL commands will run before the program starts. | |||
==Jobs== | ==Jobs== | ||
The following is a list of all programs you can attach Custom SQL commands to. Note that you will only see the programs you have licenses for. (GetStudents is | The following is a list of all programs you can attach Custom SQL commands to. Note that you will only see the programs you have licenses for. (GetStudents requires no license) | ||
==[[GetStudents]]== | |||
Always use the table '''ImportFromDB''' instead of the Students table when doing these SQL commands. | |||
[[File:Custom SQL commands GetStudents.jpg|none|thumb|725x725px]] | |||
====Description==== | |||
Enter a name. | |||
====Priority==== | |||
This control in which order commands should run. (1 is first) | |||
====SQL command==== | |||
Enter the SQL command UMS will run. | |||
You can create a Store Procedure and run that instead use the command "'''Exec''' '''<Stored Procedure Name>'''" | |||
===Add users to groups=== | |||
This option allows you to create new groups in UMS. These groups will be handled like any other group coming from the administrative system. | |||
If you only want users with 1 or more active groups add this extra SQL sentence | |||
And ((Select Case When IsNull(Slutdato, <nowiki>''</nowiki>) = <nowiki>''</nowiki> Then GetDate() + 100 Else Convert(DateTime2, Slutdato, 105) End) >= Convert(Date, GetDate())) | |||
[[File:Custom SQL commands GetStudents Add users to groups.jpg|none|thumb|725x725px]] | |||
====SQL Command==== | |||
Only write the part that comes after the SQL Where sentence and not the whole SQL sentence. | |||
====GetStudents==== | |||
All fields under this setting are text fields. | |||
The action should always be left with the text '''Add'''. | |||
[[GetTimetable]] | [[GetTimetable]] | ||
[[Moodle]] | [[Moodle]] | ||
[[Moodle#Cohort|Moodle Cohort]] | [[Moodle#Cohort|Moodle Cohort]] | ||
[[Canvas]] | [[Canvas]] | ||
[[G Suite#Drive|Google Apps Drive Sync]] | [[G Suite#Drive|Google Apps Drive Sync]] | ||
[[G Suite#Site|Google Apps Sites Sync]] | [[G Suite#Site|Google Apps Sites Sync]] | ||
[[Fronter Integration|Fronter]] | [[Fronter Integration|Fronter]] | ||
[[ELEARNINGFORCE SharePoint LMS|ELEARNINGFORCE]] | [[ELEARNINGFORCE SharePoint LMS|ELEARNINGFORCE]] | ||
[[Export User Info to Admin systems]] | [[Export User Info to Admin systems]] | ||
[[Office 365|Office 365 SharePoint Sync]] | [[Office 365|Office 365 SharePoint Sync]] | ||
[[OneNote Class Notebook]] | [[OneNote Class Notebook]] | ||
[[SLI]] | [[SLI]] | ||
==FAQ== | |||
If you ever want to use the UNILOGIN column, you'll need to run it after SLI - use the checkbox to change from before to after. | |||
Latest revision as of 12:04, 30 September 2022
Custom SQL Commands is a feature for executing customized SQL at specific times in various background jobs. The time depends on the job you attach the SQL command to.
This feature is found at: Configurator -> Settings -> Custom SQL Commands
Program
The list of UMS programs that supports Custom SQL commands. The list is only filed with programs that you have a license for.
Show commands after addon file has been run
Some of the programs have a custom file that it can run instead of the one supplied by UMS.
If you do not have a custom file then ignore this chekbox
Show commands after <Program name> has run
This allows you to run custom SQL commands after the specific program has finished running. This could be a program like SLI.
If this is not checked the custom SQL commands will run before the program starts.
Jobs
The following is a list of all programs you can attach Custom SQL commands to. Note that you will only see the programs you have licenses for. (GetStudents requires no license)
GetStudents
Always use the table ImportFromDB instead of the Students table when doing these SQL commands.
Description
Enter a name.
Priority
This control in which order commands should run. (1 is first)
SQL command
Enter the SQL command UMS will run.
You can create a Store Procedure and run that instead use the command "Exec <Stored Procedure Name>"
Add users to groups
This option allows you to create new groups in UMS. These groups will be handled like any other group coming from the administrative system.
If you only want users with 1 or more active groups add this extra SQL sentence
And ((Select Case When IsNull(Slutdato, '') = '' Then GetDate() + 100 Else Convert(DateTime2, Slutdato, 105) End) >= Convert(Date, GetDate()))
SQL Command
Only write the part that comes after the SQL Where sentence and not the whole SQL sentence.
GetStudents
All fields under this setting are text fields.
The action should always be left with the text Add.
Export User Info to Admin systems
FAQ
If you ever want to use the UNILOGIN column, you'll need to run it after SLI - use the checkbox to change from before to after.