MFSQL <database> - Set Assembly Update Flag

Also creates: Alert MFSQL <database> - M-Files Update Detected

Integration Connector only.

Job Definition

  • Job name: MFSQL <database> - Set Assembly Update Flag

  • Alert name: MFSQL <database> - M-Files Update Detected

  • Owner: sa

  • Category: Database Maintenance

  • Schedule: none - triggered entirely by the alert, not time-based

  • Alert fires on: any write to HKLM\SOFTWARE\Motive\M-Files (WMI RegistryKeyChangeEvent), throttled to at most once every 30 seconds

Purpose

Primary, immediate-detection path for keeping the CLR/Interop assemblies in sync with the installed M-Files client version. When M-Files auto-updates on the SQL Server, it writes a new version subkey under HKLM\SOFTWARE\Motive\M-Files; the alert fires within seconds and the job:

  1. Enumerates the M-Files registry subkeys to find the latest installed version, polling for up to ~30 minutes if the registry is transiently empty (M-Files removes the old version subkey before writing the new one during an upgrade, so a single alert firing can otherwise land in that empty window).

  2. Compares it against MFSettings.MFVersion.

  3. On a real change, delegates to spMFCheckAndUpdateAssemblyVersion, which sets MFSettings.MFilesUpdatePending, reloads the assemblies via spMFUpdateAssemblies, and logs the reload (MFProcessBatch/MFProcessBatchDetail, MFLog on failure).

See Validate M-Files Version Agent for the daily fallback that covers the rare case where an alert firing is missed entirely.

Diagnostics

If automatic detection is not working, check whether the alert actually exists. Note that msdb.dbo.sysalerts has no wmi_namespace/wmi_query columns: sp_add_alert stores a WMI alert’s namespace in database_name and its query in performance_condition:

SELECT name, enabled,
       database_name         AS wmi_namespace,
       performance_condition AS wmi_query
FROM msdb.dbo.sysalerts
WHERE name = N'MFSQL <database> - M-Files Update Detected';

SELECT name, enabled FROM msdb.dbo.sysjobs
WHERE name = N'MFSQL <database> - Set Assembly Update Flag';

The job and the alert are created in separate batches, so it is possible for one to exist without the other. If the alert is missing, this script records the reason in MFLog – including the error returned by sp_add_alert and the identity that executed it:

SELECT CreateDate, ErrorNumber, ErrorMessage, ProcedureStep FROM dbo.MFLog
WHERE SPName = '35.703.job.CreateMFilesUpdateAlert' ORDER BY LogID DESC;

Note that sp_add_alert requires membership of the sysadmin server role, which is a stricter requirement than the job creation in Part 1. An MFLog entry showing sysadmin=0 identifies that as the cause.

Prerequisites

  • SQL Server Agent must be running.

  • The Agent service account needs WMI access to root\default on the local host - standard domain/local service accounts have this by default.

  • On a server hosting more than one MFSQL application database, run this script once per database - the job step targets a specific application database.

Idempotency

Safe to re-run on an existing installation. Two self-heal mechanisms keep existing installs aligned with the on-disk script without operator intervention:

  • Step-content self-heal. If the existing job’s step body doesn’t match the current step version, the job (and its bound alert) is dropped and recreated with the current step - no manual “drop and recreate after every release” step required.

  • Owner self-heal. If the existing job is owned by the legacy MFSQLConnect login, the owner is updated to sa in place. An environment that has intentionally set a different sysadmin login as owner is left untouched.

  • Stale-name cleanup (one-off, 5.13.38.82). During development this script named the job and alert with an em dash rather than a hyphen as the separator. Every name-keyed check here is keyed on the current name, so an em-dash-named pair would be missed and a second, hyphen-named pair created against the same WMI query - two jobs firing spMFUpdateAssemblies on one registry write. The script now removes the em-dash-named job and alert first. This script is new in 5.13.38.82 and ships in no earlier bundle, so only internal test machines that ran a pre-release build can be affected; on every other install it is a silent no-op. No operator action is required either way.

If none of these conditions applies, re-running this script is a no-op for the existing job and alert.

Deployment

Ships in the Integration Connector deployment bundle and runs automatically during install/upgrade. Can also be re-run manually in SSMS against the target MFSQL application database.

Warnings

SQL Server Agent required. Skips cleanly (severity 10) on SQL Server Express - neither this job nor the alert is created; call spMFUpdateAssemblies manually after an M-Files upgrade instead. Job/alert owner is sa; if sa is disabled or locked in a hardened environment, the job and alert are still created but will fail to run until sa is enabled, or the owner is manually changed to another sysadmin login (e.g. leaving MFSQLConnect as a sysadmin, the standard install posture).

Changelog

Date

Author

Description

2026-07-30

AC
Alert creation (Part 2) moved into its own batch, behind its

own copy of the pre-flight guards, and wrapped in TRY/CATCH with a checked post-condition. Investigating Issue #71: across 6 installer-driven installs the job was created every time but the alert never was, while running this file directly via sqlcmd created both. The file was a single batch, so an installer-side stop after Part 1 but before Part 2 produced exactly that symptom with nothing left running to report it - and Part 2’s bare sp_add_alert call meant a failure was recorded nowhere. Failures now land in MFLog with the error detail and the executing identity (login/user/sysadmin), since sp_add_alert requires sysadmin while sp_add_job needs only SQLAgentUserRole. Guards are repeated deliberately: RETURN exits only its own batch. Severity stays at 10 throughout so a deployment batch is never aborted.

2026-07-30

AC
Remove any em-dash-named job and alert before creating the

current pair. The 5.13.38.82 ASCII-only fix changed the name separator from an em dash to a hyphen, which every name-keyed check here is keyed on - so a machine carrying the old pair would keep it and gain a second one on the same WMI query, double-firing spMFUpdateAssemblies. Legacy names are rebuilt via NCHAR(8212) to keep this file ASCII-only. This script ships in no bundle before 5.13.38.82, so only internal pre-release test installs can be affected; a no-op everywhere else.

2026-05-28

AC
Step-content self-heal: existing installs whose job/alert

step body predates a script change are dropped and recreated automatically, keyed off a version marker

2026-05-28

AC
Job owner changed from MFSQLConnect to sa so the step’s

sysadmin-only assembly reload can succeed; added idempotent owner self-heal for existing installs

2026-05-27

AC
Alert-timing fix: job step now self-polls the registry for

a bounded ~30 min window so a single alert firing survives the M-Files install’s transitional empty-registry window

2026-05-27

AC
Add SQL Server Express edition guard; corrected deployment

note - script ships in the Integration bundle and auto-executes

2026-05-20

AC

Pre-flight check for the MFSQLConnect SQL login

2026-04-22

AC
Initial draft; job step calls spMFUpdateAssemblies

immediately once a version change is detected