spMFUpdateTableinBatches¶

Returns

1 = Success
-1 = Error

Parameters

@MFTableName nvarchar(128)

Valid Class TableName as a string
Pass the class table name, e.g.: ‘MFCustomer’

@UpdateMethod int

Default to 1 (From MF to SQL)
Set to 0 for updates from SQL to MF

@WithTableAudit int

Default = 0 (table audit not included)
Set to 1 to trigger a table audit on the selected objids. Changes in M-Files will be overwritten by SQL.

@FromObjid bigint

Starting objid. Only applicable for updatemethod 1

@ToObjid bigint

End objid inclusive. Only applicable for updatemethod 1
Default = 1 000 000

@WithStats bit

Default = 0 (false)
When true a log will be produced in the SSMS message window to show the progress
Set to 0 to suppress the messages.

@RetainDeletions bit

Default = 0 (no)
Set to 1 to retain the deleted records in the class table

@ProcessBatch_ID int (optional, output)

Referencing the ID of the ProcessBatch logging table

@Debug smallint (optional)

Default = 0
1 = Standard Debug Mode

Purpose¶

Procedure to update a class table in batches.

Additional Info¶

When updating a large number of records from a specific class in MF to SQL it is advisable to process these updates of large datasets in batches. Processing batches will ensure that a logical restart point can be determined in case of failure or to control the updating in large chunks. It will also keep the size of the dataset for transfer within the limits of the XML transfer file.

Prerequisites¶

Best practice: provide the maximum object id in the Object Type + 500 as @ToObjid instead of relying on the default of 1,000,000. One way to obtain the maximum is via a view in M-Files on the Segment ID.

Warning¶

The FromObjid and ToObjid parameters is ignored when updatemethod = 0. These parameters are only used for updatemethod = 1.

When @WithTableAudit is specified for @UpdateMethod = 0 the latest version is determined for all objects with process_id = 1. Where different, the update won’t throw a synchronization error and changes in M-Files will be overwritten by SQL.

Examples¶

Update SQL to MF

EXEC dbo.spMFUpdateTableinBatches @MFTableName = 'YourTable'
                                       ,@UpdateMethod = 0
                                       ,@WithStats = 1
                                       ,@Debug = 0;

Update SQL to MF - synchronization errors will be overwritten from SQL

EXEC dbo.spMFUpdateTableinBatches @MFTableName = 'YourTable'
                                       ,@WithTableAudit = 1
                                       ,@UpdateMethod = 0
                                       ,@WithStats = 1
                                       ,@Debug = 0;

Update MF to SQL: class table initialization (note the @WithTableAudit setting)

EXEC dbo.spMFUpdateTableinBatches @MFTableName = 'YourTable'
                                       ,@UpdateMethod = 1
                                       ,@WithTableAudit = 1
                                       ,@FromObjid = 1
                                       ,@ToObjid = 1000
                                       ,@WithStats = 1
                                                                                       ,@Debug = 0;

Update MF to SQL: retain deleted objects in the class table

EXEC dbo.spMFUpdateTableinBatches @MFTableName = 'YourTable'
                                       ,@UpdateMethod = 1
                                       ,@WithTableAudit = 1
                                       ,@FromObjid = 1
                                       ,@ToObjid = 1000
                                       ,@WithStats = 1
                                       ,@RetainDeletions = 1
                                       ,@Debug = 0;

Changelog¶

Date	Author	Description
2025-05-21	LC	Fix bug with not updating from SQL to MF when only new objects is in list
2025-04-30	LC	Prevent update to MF if @objids is null
2025-04-30	LC	Exclude unwanted processing message
2024-10-12	LC	Change default of @WithStats to 0
2024-04-10	LC	Use of WithTableAudit and updatemethod 0 will ignore synch errors and overwrite changes in MF
2024-04-10	LC	change approach for updatemethod 0 to work with id instead of objid
2024-01-26	LC	set ansi warning off
2024-01-26	LC	resolve not processing when only a new record is being processed
2023-12-08	LC	resolve issue when from to objid is used with updatemethod 0
2022-10-27	LC	add retaindeletions to spmfupdatetable processing
2021-08-25	LC	add output to the processbatch_id parameter
2021-05-03	LC	Fix bug to include first record of each batch
2020-09-24	LC	Set updatetable objids to include unmatched versions
2020-09-23	LC	Fix batch size calculation
2020-09-04	LC	Fix null count or set operation
2020-08-23	LC	Add parameter to retain deletions, default set to NO
2019-12-18	LC	include status flag 6 from AuditTable
2019-06-22	LC	substantially rebuilt to improve efficiencies
2019-08-05	LC	resolve issue with catching last object if new and only one object exist
2018-12-15	LC	Create procedure