Using update filters: last modified date¶
Filter MF->SQL updates using the last modified timestamp; includes helper proc variant.
Reviewing the current status of updates
EXEC spMFClassTableStats 'MFCustomer' -- note the lastmodified and mflastmodified dates - it is the Max(date) on the table
make a change in M-Files to customer, then the following procedures allows you to specify the MFLastModified date as the filter for updating from MF to SQL
DECLARE @Update_IDOut INT,
@ProcessBatch_ID INT,
@LastModifiedDate DATETIME;
SELECT @LastModifiedDate = MAX(@LastModifiedDate) FROM dbo.MFCustomer AS mc
EXEC dbo.spMFUpdateTable @MFTableName = 'MFCustomer', -- nvarchar(200)
@UpdateMethod = 1, -- int
@MFModifiedDate = @LastModifiedDate, -- datetime
@Update_IDOut = @Update_IDOut OUTPUT, -- int
@ProcessBatch_ID = @ProcessBatch_ID OUTPUT, -- int
@Debug = 0 -- smallint
SELECT * FROM dbo.MFProcessBatchDetail AS mpbd WHERE mpbd.ProcessBatch_ID = @ProcessBatch_ID
the following procedure has the mfUpdateTable with the last modified date switch built in. It will always reference to max(MFLastModified) date with no need to first define and set it
DECLARE @ReturnDate DATETIME, @ProcessBatch_ID INT, @Update_ID int
EXEC spMFUpdateTableWithLastModifiedDate @UpdateMethod = 1
, @Return_LastModified = @ReturnDate OUTPUT
, @TableName = 'MFCustomer'
,@Update_IDOut = @Update_ID output
,@ProcessBatch_ID = @ProcessBatch_ID output
SELECT @ReturnDate
SELECT * FROM dbo.MFUpdateHistory AS muh WHERE id = @Update_ID
SELECT * FROM dbo.MFProcessBatchDetail AS mpbd WHERE mpbd.ProcessBatch_ID = @ProcessBatch_ID