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) |