spMFUpdateTableinBatches¶

Return

1 = Success
-1 = Error

Parameters

@MFTableName

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 = 1 (true)
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 (optional, output)

Referencing the ID of the ProcessBatch logging table

@Debug (optional)

Default = 0
1 = Standard Debug Mode

Purpose¶

Procedure to update 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¶

It is good practice to provide the maximum object id in the Object Type + 500 as the @ToObjid instead of just working with the default of 100 000. One way to obtain the maximum is to use 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 then the latest version of the object will be determined for all objects with process_id 1. Where different, the update will not through a Synchronization error and any Changes in M-Files will be overwritten with the changes in 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 initialisation (note the setting with @WithtableAudit)

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

Update MF to SQL : Retain the 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
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