spMFUpdateMFilesToMFSQL¶

Return

1 = Success
-1 = Error

Parameters

@MFTableName nvarchar(128)

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

@MFLastUpdateDate smalldatetime (output)

returns the most recent MF Last modified date

@UpdateTypeID tinyint (optional)

1 = incremental update (default)
0 = Full update

@MaxObjects INT

Default = 100000
if UpdateTypeID = 0 then this parameter must be set if there are more than 100000 objects in the objecttype

@WithObjectHistory BIT

Default = 0 (No)
set to 1 to include updating the object history

@RetainDeletions BIT

Default = 0 (deletions will be removed from class table)
set to 1 to retain any deletions since the last update

@WithStats BIT

default = 0
Set to 1 to show progress of processing

@Update_IDOut int (output)

returns the id of the last updated batch

@ProcessBatch_ID int (optional, output)

Referencing the ID of the ProcessBatch logging table

@Debug tinyint (optional)

Default = 0
1 = Standard Debug Mode
101 = Advanced Debug Mode

Purpose¶

The purpose of this procedure has migrated over time from processing records by objid to a routine that can be used by default for large and small tables to process records from M-Files to SQL. The procedure is fundamentally based on updating M-Files to SQL using a rapid evaluation of the object version of each object and then to update based on the object id of the object.

Additional Info¶

Setting UpdateTypeID = 0 (Full update) will perform a full audit of the class table by validating every object version in the class and run through an update of all the objects where the version in M-Files and SQL are not identical.

This will run spmfUpdateTableinBatches in silent mode. Note that the Max Objid to control the update is derived as the max(objid) in the class table + 500 of the class table. Setting UpdateTypeID = 1 (incremental update) will perform an audit of the class table based on the date of the last modified object in the class table, and then update the records that is not identical

Deleted records in M-Files will be identified and removed.

The following importing scenarios apply:

If the file already exist for the object then the existing file in M-Files will be overwritten. M-Files version control will record the prior version of the record.
If the object is new in the class table (does not yet have a objid and guid) then the object will first be created in M-Files and then the file will be added.
If the object in M-Files is a multifile document with no files, then the object will be converted to a single file object.
if the object in M-files already have a file or files, then it would convert to a multifile object and the additional file will be added
If the filename or location of the file cannot be found, then a error will be added in the filerror column in the MFFileImport Table.
If the parameter option @IsFileDelete is set to 1, then the originating file will be deleted. The default is to not delete.
The MFFileImport table keeps track of all the file importing activity.
If the flag @WithObjectHistory is set 1 then an incremental object change history will be performed.

Warnings¶

Use spmfUpdateTableInBatches to initiate a class table instead of this procedure.

When @updateTypeID is set to 0 and the maximum objid of the object type is more than 100 000 then the @MaxObjects parameter must be set

To initialise object Change history for a class or add a new property for the object change history, use spMFUpdateObjectChangeHistory instead of this procedure. This procedure only performs object change history updates where some object changes have already been recorded in MFObjectChangehistory and the object versions for all the properties defined in the control table is available.

The incremental updating of the object change history is based on the version of the object in the object change history and is dependent on the entries in MFObjectChangeHistoryUpdateControl

Examples¶

Full update of class table. Set parameter @MaxObjects to the maximum oobject id in the object type when greater than 100000 to ensure that the audit process will run in batches.

DECLARE @MFLastUpdateDate SMALLDATETIME
   ,@Update_IDOut     INT
   ,@ProcessBatch_ID  INT;

EXEC [dbo].[spMFUpdateMFilesToMFSQL] @MFTableName = 'YourTable'
                                ,@MFLastUpdateDate = @MFLastUpdateDate OUTPUT
                                ,@UpdateTypeID = 0
                                ,@MaxObjects = 500000
                                ,@Withstats = 1
                                ,@Update_IDOut = @Update_IDOut OUTPUT
                                ,@ProcessBatch_ID = @ProcessBatch_ID OUTPUT
                                ,@debug = 0;

SELECT @MFLastUpdateDate AS [LastModifiedDate];

For incremental updates

DECLARE @MFLastUpdateDate SMALLDATETIME
   ,@Update_IDOut     INT
   ,@ProcessBatch_ID  INT;


EXEC [dbo].[spMFUpdateMFilesToMFSQL] @MFTableName = 'YourTable'
                                ,@MFLastUpdateDate = @MFLastUpdateDate OUTPUT
                                ,@UpdateTypeID = 1
                                ,@Update_IDOut = @Update_IDOut OUTPUT
                                ,@ProcessBatch_ID = @ProcessBatch_ID OUTPUT
                                ,@debug = 0;

SELECT @MFLastUpdateDate;

Changelog¶

Date	Author	Description
2024-03-25	LC	fix bug in determining which object changes to include in incremental update
2023-11-10	LC	prevent update if @objids is null
2023-11-09	LC	remove resetting process 3 and 4
2023-08-16	LC	resolve bug for deleting not in class records
2023-08-15	LC	deal with null value warning
2022-06-06	LC	resolve issue of removal of class table objects
2022-05-06	LC	resolve bug with nextbatch_ID
2022-01-25	LC	allow for batch processing of audit when max object > 100000
2022-01-25	LC	increase maxobjects default to 100000
2021-12-20	LC	Maintain same processbatch_ID for entire process
2021-12-20	LC	Revise removal of deleted objects from table
2021-12-16	LC	Remove deletion of audit table with full update
2021-08-28	LC	with full update, remove objects in class table not in audit table
2021-07-03	LC	improve debugging and error reporting
2021-05-11	LC	redesign the grouping of objects to overcome persistent issues
2021-05-10	LC	add controls to validate group list creation
2021-04-26	LC	add removal of redundant class records
2021-03-17	LC	include audit statusflag =1 into incremental update
2021-03-17	LC	resolve issue where objid for exist for class in two objecttypes
2021-03-16	LC	Remove object where class has changed from audit table
2021-03-11	LC	fix objlist error when both class and audit objid is null
2021-03-10	LC	fix updatechangehistory when control table empty
2021-01-07	LC	Include override to recheck any class objects not in Audit
2020-09-04	LC	Resolve bug with full update
2020-08-23	LC	replace get max objid with index update
2020-08-23	LC	Add parameter to retain deletions, default set to NO.
2020-08-22	LC	Elliminate use of get deleted records
2020-04-23	LC	Set maxobjects
2020-03-06	LC	Add updating of object history
2020-02-14	LC	Resolve skipped audit items where class missing items
2019-12-10	LC	Add a parameter to set the maximum number of objects in class
2019-09-27	LC	Set withstats for audit batches = 0
2019-09-27	LC	Fix UpdateID in MFProcessBatchDetail
2019-09-03	LC	Set audittableinbatches to withstats = 0
2019-09-03	LC	Set default date for deleted record check to 2000-01-01
2019-08-30	JC	Added documentation
2019-08-05	LC	Fix bug in updating single record
2019-04-12	LC	Allow for large tables
2018-10-22	LC	Align logtext description for reporting, refine ProcessBatch messages
2018-10-20	LC	Fix processing time calculation
2018-05-10	LC	Add error if invalid table name is specified
2017-12-28	LC	Add routine to reset process_id 3,4 to 0
2017-12-25	LC	Change BatchProcessDetail log text for lastupdatedate
2017-06-29	AC	Change LogStatusDetail to ‘Completed’ from ‘Complete’
2017-06-08	AC	Incorrect LogTypeDetail value
2017-06-08	AC	ProcessBatch_ID not passed into spMFTableAudit
2016-08-11	AC	Create Procedure