spMFTableAudit

Signature

EXEC dbo.spMFTableAudit
    @MFTableName = N'MFCustomer',
    @MFModifiedDate = NULL,
    @ObjIDs = NULL,
    @SessionIDOut = NULL OUTPUT,
    @NewObjectXml = NULL OUTPUT,
    @DeletedInSQL = NULL OUTPUT,
    @UpdateRequired = NULL OUTPUT,
    @OutofSync = NULL OUTPUT,
    @ProcessErrors = NULL OUTPUT,
    @ProcessBatch_ID = NULL OUTPUT,
    @Debug = 0;

Returns

INT: 1 on success; -1 on error.

Parameters

@MFTableName
  • Type: NVARCHAR(128)

  • Required: Yes

  • Description: Class table name (e.g., ‘MFCustomer’).

@MFModifiedDate
  • Type: DATETIME

  • Required: No (default = NULL)

  • Description: Filter by M-Files last modified date; NULL selects all records.

@ObjIDs
  • Type: NVARCHAR(4000)

  • Required: No (default = NULL)

  • Description: Comma-delimited ObjIDs to process; NULL to include all.

@SessionIDOut
  • Type: INT

  • Required: No (OUTPUT)

  • Description: Session id used to update MFAuditHistory.

@NewObjectXml
  • Type: NVARCHAR(MAX)

  • Required: No (OUTPUT)

  • Description: ObjVer result as NVARCHAR (convertible to XML).

@DeletedInSQL
  • Type: INT

  • Required: No (OUTPUT)

  • Description: Number of items that will be marked as deleted on next update.

@UpdateRequired
  • Type: BIT

  • Required: No (OUTPUT)

  • Description: 1 if MF and SQL differ; can be used to trigger a subsequent update.

@OutofSync
  • Type: INT

  • Required: No (OUTPUT)

  • Description: > 0 indicates potential sync errors during the next update.

@ProcessErrors
  • Type: INT

  • Required: No (OUTPUT)

  • Description: > 0 indicates unresolved errors (process_id 3 or 4).

@ProcessBatch_ID
  • Type: INT

  • Required: No (OUTPUT)

  • Description: References the ID of the ProcessBatch logging table.

@Debug
  • Type: SMALLINT

  • Required: No (default = 0)

  • Description: Debug level.

Purpose

Update MFAuditHistory and return the sessionid and the M-Files objver of the selection class as a varchar that can be converted to XML if there is a need for further processing of the result.

Additional Info

At the same time spMFTableAudit will set the deleted flag for all the records in the Class Table that is deleted in M-Files. This is particularly relevant when this procedure is used in conjunction with the spMFUpdateTable procedure with the filter MFLastModified set.

Examples

DECLARE @SessionIDOut INT
       ,@NewObjectXml NVARCHAR(MAX)
       ,@DeletedInSQL INT
       ,@UpdateRequired BIT
       ,@OutofSync INT
       ,@ProcessErrors INT
       ,@ProcessBatch_ID INT;

EXEC [dbo].[spMFTableAudit]
           @MFTableName = N'MFCustomer' -- nvarchar(128)
          ,@MFModifiedDate = null -- datetime
          ,@ObjIDs = null -- nvarchar(4000)
          ,@SessionIDOut = @SessionIDOut OUTPUT -- int
          ,@NewObjectXml = @NewObjectXml OUTPUT -- nvarchar(max)
          ,@DeletedInSQL = @DeletedInSQL OUTPUT -- int
          ,@UpdateRequired = @UpdateRequired OUTPUT -- bit
          ,@OutofSync = @OutofSync OUTPUT -- int
          ,@ProcessErrors = @ProcessErrors OUTPUT -- int
          ,@ProcessBatch_ID = @ProcessBatch_ID OUTPUT -- int
          ,@Debug = 0 -- smallint

SELECT @SessionIDOut AS 'session', @UpdateRequired AS UpdateREquired, @OutofSync AS OutofSync, @ProcessErrors AS processErrors
SELECT * FROM [dbo].[MFProcessBatch] AS [mpb] WHERE [mpb].[ProcessBatch_ID] = @ProcessBatch_ID
SELECT * FROM [dbo].[MFProcessBatchDetail] AS [mpbd] WHERE [mpbd].[ProcessBatch_ID] = @ProcessBatch_ID

Changelog

Date

Author

Description

2023-06-30

LC

allow for specifying date in UTC

2023-04-05

LC

refine options for selecting default date

2022-09-08

LC

simplify query for flag 5 to ellimate objects not in Audit table

2022-01-28

LC

set objids datatype to max

2022-01-08

LC

new code to deal with class changes

2021-12-20

LC

add revalidate of deleted objects when incremental update

2021-12-20

LC

add checking of objvers where full update did not update them

2021-12-16

LC

Add additional logging for performance monitoring

2021-04-01

LC

Add statusflag for Collections

2020-09-08

LC

Update to include status code 5 object does not exist

2020-09-04

LC

Add update locking and commit to improve performance

2020-08-22

LC

update to take into account new deleted column

2019-12-10

LC

Fix bug for the removal of records from class table

2019-10-31

LC

Fix bug - change Class_id to Class in delete object section

2019-09-12

LC

Fix bug - remove deleted objects from table

2019-08-30

JC

Added documentation

2019-08-16

LC

Fix bug for removing destroyed objects

2019-06-22

LC

Objid parameter not yet functional

2019-05-18

LC

Add additional exception for deleted in SQL but not deleted in MF

2019-04-11

LC

Fix collection object type in table

2019-04-11

LC

Add large table protection

2019-04-11

LC

Add validation table exists

2018-12-15

LC

Add ability to get result for selected objids

2018-08-01

LC

Resolve issue with having try catch in transaction processing

2017-12-28

LC

Change insert to merge on audit table

2017-12-27

LC

Remove incorrect error message

2017-08-28

LC

Add param for update required

2017-08-28

LC

Add logging

2017-08-28

LC

Change sequence of params

2016-08-22

LC

Change objids to NVARCHAR(4000)