Using Agent for automated updates

SQL Agent is used to schedule a range of operations for the Connector. The installation package will automatically install two agents.  Other agents can be used to perform any number of operations. Updating class tables on a schedule is very common.

Agents will be installed for each Connector database for the following

  1. MFSQL Delete History

  2. MFSQL Validated M-Files Version

  • To Delete history: MFSQL Delete History for Databasename

  • To Validate the M-Files version: MFSQL Validate `<database>` M-Files Version

By default these agents are not enabled. Select the agent, then select Schedules, and edit the schedule by selecting the enabled button.

The following agent is installed when the enabling procedure spMFSetup_Reporting is executed

  • To update all class tables

Update Class Tables

The installation package does not automatically create an agent for scheduling the update of the class tables.

For incremental updates use spMFUpdateAllncludedInAppTables in the agent.

This procedure provides for:

  • Update all tables included in the app.

  • Incremental updates for all changes in M-Files since the last class table update. This does not catch deleted and destroyed objects.

  • Full update to catch deleted or destroyed objects

  • Include deleted objects for all class tables. By default, deleted records are removed from the class table.

  • Specify the update for a subset classes. Include by default all the tables where IncludeInApp column in MFClass has 1. However, by setting the column to a different figure the tables can be grouped together. This is particularly handy to run this procedure on subsets of class tables.

  • Email a status report for all tables with errors to a specified user

Include the following script as a step in the agent and schedule to job to run with your required intervals

The shortage interval should not be less that the run time of the procedure.

 EXEC [dbo].[spMFUpdateAllncludedInAppTables]
 @UpdateMethod = 1
,@IsIncremental = 1

For full updates use spMFUpdateAllncludedInAppTables in the agent. Include the following script as a step in the agent.

 EXEC [dbo].[spMFUpdateAllncludedInAppTables]
 @UpdateMethod = 0
,@IsIncremental = 1

Delete History Agent

Job name: MFSQL Delete History for `<database>`.

To enable: in SSMS, expand SQL Server Agent → Jobs, select the job, open Schedules, and enable it. Not enabled by default; runs monthly once enabled.

See MFSQL Delete History for <database> for full job details (tables affected, warnings, SQL Server Express behavior).

WMI Update Alert (Integration Connector)

Job name: MFSQL `<database>` — Set Assembly Update Flag. Alert name: MFSQL `<database>` — M-Files Update Detected.

Since MFSQL Connector 5.13.38.82, an automated WMI alert is the primary mechanism that keeps the connector’s assemblies in sync with the installed M-Files version — Integration Connector only. Ships in the Integration Connector bundle and installs automatically; no manual step is normally required. On a SQL Server hosting more than one MFSQL application database, run the script once per database.

See MFSQL <database> - Set Assembly Update Flag for full job details (detection mechanism, prerequisites, warnings).

Validate M-Files Version Agent

Job name: MFSQL Validate `<database>` M-Files Version.

To enable: in SSMS, expand SQL Server Agent → Jobs, select the job, open Schedules, and enable it. Not enabled by default; runs daily once enabled.

Note

Since MFSQL Connector 5.13.38.82, this daily Agent job is a fallback to WMI Update Alert (Integration Connector) above, which detects an M-Files version change within seconds. This job exists to catch the rare case where an alert firing is missed.

See MFSQL Validate <database> M-Files Version for full job details (owner, warnings, SQL Server Express behavior).

Staging updates with agents

Sometimes it is necessary to ensure a job is completed before it is proccessed again, or to check if a agent is running before a procedure is executed. The following provides more details to assist with this consideration.