spMFDropAndUpdateMetadata¶

Return

1 = Success
-1 = Error

Parameters

@IsResetAll smallint (optional)

Default = 0
1 = Reset to default values

@WithClassTableReset smallint (optional)

Default = 0
1 = reset all class tables included in App

@WithColumnReset smallint (optional)

Default = 0
1 = automatically reset column datatypes where datatypes changed

@IsStructureOnly smallint (optional)

Default = 0
1 = include updating of all valuelist items or only main structure elements

@RetainDeletions bit

Default = No
Set explicity to 1 if the class table should retain deletions

@IsDocumentCollection

Default = No
Set explicitly to 1 if the class table refers to a document collection class table

@ProcessBatch_ID int (optional, output)

Referencing the ID of the ProcessBatch logging table

@Debug smallint (optional)

Default = 0
1 = Standard Debug Mode

Purpose¶

To drop and update metadata for usage when creating multiple iterations of metadata and table changes during development.

Additional Info¶

This procedure will only run if metadata structure changes were made. It is therefore useful to add this procedure as a scheduled agent, or as part of key procedures to keep the structure aligned.

Using this procedure will not overwrite custom settings in the structure tables. The custom columns include:

Table	Customisable columns
MFClass	IncludedInApp
MFClass	TableName
MFClass	FileExportFolder
MFClass	SynchPresendence
MFProperty	ColumnName
MFValuelistItems	AppRef
MFValuelistItems	Owner_AppRef

This procedure can also be used to reset all the metadata, but retain the custom settings in the Tables when the default is used or @ISResetAll = 0.

Set @ISResetAll = 1 only when custom settings in SQL should be reset to the defaults. The following custom settings in the metadata tables.?

Setting the parameter @WithClassTableReset = 1 will drop and recreate all class tables where IncludeInApp = 1. This is particularly usefull during testing or development to reset the class tables. This parameter is set to 0 by default.

Setting the parameter @WithColumnReset = 1 will force the synchronisation to add missing properties to class tables. This is particularly handy when a property is added to multiple classes on the metadata cards and requires pull through to the class tables in SQL. It will also change single lookup to multi lookup columns or visa versa. This parameter is set to 0 by default.

Use spMFClassTableColumns to review the application and status of properties and columns on class tables.

By default this procedure will not be triggered if only valuelist items have been added in M-Files or no metadata changes have taken place. To force this procedure to run, set the @IsStructureOnly = 0 to force an update in this scenario.

Warnings¶

Do no run other procedures (such as spmfupdatetable) while any syncrhonisation of metadata is in progress.

The runtime of this procedure has increased, especially for large complex vaults. This is due to the extended validation checks performed during the procedure.

Not all metadata changes increases the GetMetadataStructureVersionID in M-Files. Changes to valuelist items does not set a version change for metadata changes.

The default options is not appropriate when valuelist items must be included in the update. There are several other methods to achieve the update of valuelist items rapidly, for instance spMFSynchronizeSpecificMetadata

Examples¶

Standard use without any parameters. This will retain all custom settings and only run if changes in M-Files have been detected.

EXEC spMFDropAndUpdateMetadata

Running the procedure with default settings and no structure metadata change has taken place will exit very rapidly.

DECLARE @ProcessBatch_ID INT;
EXEC [dbo].[spMFDropAndUpdateMetadata] @IsResetAll = 0
                                      ,@WithClassTableReset = 0
                                      ,@WithColumnReset = 0
                                      ,@IsStructureOnly = 1
                                      ,@ProcessBatch_ID = @ProcessBatch_ID OUTPUT
                                      ,@Debug = 0

To force an update of metadata when only valuelist items have changed or no metadata change has taken place, set the @IsStructureOnly = 0.

EXEC [dbo].[spMFDropAndUpdateMetadata]
           @IsStructureOnly = 0

The parameter @IsResetAll will remove all custom settings in SQL and reset the metadata structure to the vault. This include removing all the class tables. This should only be used as a tool during prototyping and testing use cases.

EXEC [dbo].[spMFDropAndUpdateMetadata]
           @IsResetAll = 1

—

To reset columns when data types have changed, set the @WithColumnReset = 1

EXEC [dbo].[spMFDropAndUpdateMetadata]
          ,@WithColumnReset = 1
          ,@IsStructureOnly = 0

Changelog¶

Date	Author	Description
2024-04-01	LC	improve logging of ProcessBatch
2023-12-28	LC	Improve error correction and table updates
2023-12-18	LC	fix error with delimiter in reset functionality
2023-07-30	LC	Improve logging and update processing
2023-04-19	LC	Improve with column reset functionality
2021-09-30	LC	Update documentation regarding column fixes
2020-09-08	LC	Add fixing column errors in datatype 9
2019-08-30	JC	Added documentation
2019-08-27	LC	If exist table then drop, avoid sql error when table not exist
2019-08-06	LC	Change of metadata return value, remove if statement
2019-06-07	LC	Fix bug of not setting lookup table label column with correct type
2019-03-25	LC	Fix bug to update when change has taken place and all defaults are specified
2019-01-20	LC	Add prevent deleting data if license invalid
2019-01-19	LC	Add new feature to fix class table columns for changed properties
2018-11-02	LC	Add new feature to auto create columns for new properties added to class tables
2018-09-01	LC	Add switch to destinguish between structure only on including valuelist items
2018-06-28	LC	Add additional columns to user specific columns fileexportfolder, syncpreference
2017-06-20	LC	Fix begin tran bug