uspSetArchivingDataObject

Purpose

The uspSetArchivingDataObject stored procedure is designed to register, update, and delete data objects for the archiving process within the ExMeX Archive Framework.

Motivation

Data archiving is vital for ensuring that outdated or inactive data is stored efficiently and securely, while remaining accessible when needed. Over time, archived data may need to be purged to optimize storage costs and maintain system performance.

Applicability

The stored procedure uspSetArchivingDataObject registers data objects in the ExMeX Archiving Framework for archiving.

Version Information

• Last Updated: 2025-03-06
• File Version: 1.1.0
• Copyright: (C) 2017 - 2025 Dirk Lerner | TEDAMOH GmbH

Version History

1.1.0

• Added: 0000680: Check if target schema exists
• Added: 0000679: Change column code for archiving modules
• Added: 0000676: Archiving Framework: Order Archiving Objects
• Added: 0000696: Archiving Framework: Usage of year

1.0.0

• Initial release

Syntax and Usage

Below is an example of the stored procedure's structure:

EXEC MetadataZoneCore.uspSetArchivingDataObject
    -- Action to perform: (A)dd, (U)pdate, (D)elete
    @SetArchivingExec          = <(A)dd, (U)pdate, (D)elete>,

    -- Source data object to be archived (mandatory)
    @SourceDatabaseName        = <optional - defaults to the current database if not submitted>,
    @SourceSchemaName          = <mandatory - source schema name>,
    @SourceDataObject          = <mandatory - source data object>,

    -- Source data item, used to identify records for archiving (mandatory only when registering a new data object)
    @ArchivingColumnCode       = <column name to apply archiving>,

    -- Target archiving object where data will be archived (data is physically deleted from this object)
    @ArchivingDatabaseName     = <optional - defaults to 'SourceDatabaseName'>,
    @ArchivingSchemaName       = <optional - defaults to 'SourceSchemaName' + 'Archive'>,
    @ArchivingDataObject       = <optional - defaults to 'SourceDataObject' + 'Archive'>,

    -- Options - if nothing is set, default will be applied
    @IsActive                  = <0 = OFF (default); 1 = ON>,
    @RetentionPeriod           = <days - how long to retain data before archiving, default = NULL>,
    @ArchivingRetentionPeriod  = <days - how long to retain archived data before deletion, default = NULL>,
    @ArchivingExecutionOrder   = <order applied during execution, default = NULL>,
    @Comment                   = <comments or description>,

    -- For troubleshooting purposes
    @Debug                     = <optional debug flag: 0 = OFF (default); 1 = ON>

Input Parameters

Parameter	Type	Mandatory	Description
@SetArchivingExec	CHAR(1)	Yes	Specifies the operation: (A)dd, (U)pdate, or (D)elete a data object
@SourceDatabaseName	NVARCHAR(128)	No	Database name for the source data object. Defaults to current database DB_NAME()
@SourceSchemaName	NVARCHAR(128)	Yes	Schema name for the source data object
@SourceDataObject	NVARCHAR(128)	Yes	Name of the source data object to be archived
@ArchivingColumnCode	NVARCHAR(128)	Conditional	Column that determines when data should be archived. Mandatory for Add (A), optional for Update (U) and Delete (D). See Date Format for valid formats
@ArchivingDatabaseName	NVARCHAR(128)	No	Database for the archiving data object. Defaults to @SourceDatabaseName
@ArchivingSchemaName	NVARCHAR(128)	No	Schema name for the archiving data object. Defaults to @SourceSchemaName + "Archive"
@ArchivingDataObject	NVARCHAR(128)	No	Name of the archiving data object. Defaults to @SourceDataObject + "Archive"
@IsActive	BIT	No	Activates (1) or deactivates (0) the archiving process. Default is OFF (0)
@RetentionPeriod	INT	No	Number of days data should be retained in the source before archiving
@ArchivingRetentionPeriod	INT	No	Number of days data should be retained in the archive before deletion
@ArchivingExecutionOrder	INT	No	Execution order for archiving. Lower values are processed first
@Comment	NVARCHAR(MAX)	No	Additional comments or descriptions
@Debug	TINYINT	No	Debug mode: 0 = OFF (default), 1 = ON with verbose output

Return Types

This stored procedure returns an integer value indicating the execution status.

Return Values

Return Value	Description
0	Execution successful
1	Source database does not exist
2	Source schema or data object does not exist
3	Specified archiving column does not exist in the source data object
4	Missing index on archiving column in source data object
5	The archiving data object already exists (when adding)
6	The archiving schema does not exist
7	The archiving column does not contain a valid format for archiving
8	Adding archiving data object failed
9	Deleting archiving data object failed

When a data object is successfully registered, updated, or deleted, the stored procedure will return status 0.

Considerations and Impacts

The same considerations and impacts detailed in the ExMeX Archiving Framework apply when using this stored procedure.

Date Format for Archiving Column

The column used to archive data must contain data that can be converted into a date. The stored procedure performs comprehensive validation to ensure the column contains valid date information.

Supported Data Types:

• date
• datetime
• datetime2
• String representations of dates
• Integer representations (e.g., YYYYMMDD, YYYYMM, YYYY)

Examples of Valid Date Formats:

Format	Example	Description
ISO Date	2025-02-26	Standard ISO format
Year Only	2025	Year for annual archiving
Year-Month	202502	Year and month (YYYYMM)
Year-Month-Day	20250226	Compact date format (YYYYMMDD)
DateTime String	Dec 2 2023 12:00AM	SQL Server datetime string
DateTime2	2025-03-06 14:17:34.3366857	High precision datetime

Validation Process:

The stored procedure validates the archiving column through two checks:

1. Direct Date Validation: Tests if the column content can be directly converted to a date
2. Extended Format Validation: For year/month formats, appends '01' and validates (e.g., 202502 → 20250201)
3. Type Check: Verifies if the column is of type datetime2

Testing Column Validity:

If you are not sure whether the content of the selected column can be used for archiving, use the following SQL statement for testing:

-- Declare variables
DECLARE @SourceDatabaseName NVARCHAR(128) = N'<your database name>'
DECLARE @SourceSchemaName NVARCHAR(128) = N'<your schema name>'
DECLARE @SourceDataObject NVARCHAR(128) = N'<your data object name>'
DECLARE @ArchivingColumnCode NVARCHAR(128) = N'<your archiving column name>'
DECLARE @Sql NVARCHAR(MAX) = N''

-- Test if archiving column code contains valid format
SET @Sql = N'
IF (SELECT TOP 1 ISDATE(CAST([' + @ArchivingColumnCode + '] AS VARCHAR(24)))
    FROM [' + @SourceDatabaseName + '].[' + @SourceSchemaName + '].[' + @SourceDataObject + ']) <> 0
    SELECT ''VALID format''
ELSE
BEGIN
    IF (SELECT ISDATE(CONCAT(CAST(MAX([' + @ArchivingColumnCode + ']) AS VARCHAR(24)),''01''))
        FROM [' + @SourceDatabaseName + '].[' + @SourceSchemaName + '].[' + @SourceDataObject + ']) <> 0
        SELECT ''VALID format''
    ELSE
    BEGIN
        IF EXISTS (
            SELECT ''VALID format''
            FROM [' + @SourceDatabaseName + '].sys.objects so
            INNER JOIN [' + @SourceDatabaseName + '].sys.schemas ss
                ON so.schema_id = ss.schema_id
            INNER JOIN [' + @SourceDatabaseName + '].sys.syscolumns sc
                ON so.object_id = sc.id
            INNER JOIN [' + @SourceDatabaseName + '].sys.types t
                ON sc.xtype = t.user_type_id
            WHERE so.type = N''U''
            AND so.name = N''' + @SourceDataObject + '''
            AND ss.name = N''' + @SourceSchemaName + '''
            AND sc.name = N''' + @ArchivingColumnCode + '''
            AND t.name = ''datetime2''
        )
            SELECT ''VALID format''
        ELSE
            SELECT ''INVALID format''
    END
END';

EXEC (@Sql)

Index Requirements

Critical: The archiving column must have an index on the source data object for optimal performance. The stored procedure automatically checks for index existence and will fail with return code 4 if no index is found.

When the index is missing, the procedure provides a recommendation for creating the index:

CREATE NONCLUSTERED INDEX [IX_<SchemaName>_<DataObject>_<ArchivingColumn>]
    ON [<DatabaseName>].[<SchemaName>].[<DataObject>] ([<ArchivingColumn>] ASC)
    WITH (DATA_COMPRESSION = PAGE)

Automatic Archive Data Object Creation

When adding a new archiving data object (operation 'A'), the stored procedure automatically:

1. Creates the archive table with the same structure as the source table
2. Creates a clustered index on the archiving column
3. Applies page compression to optimize storage
4. Registers the data object in MetadataZoneCore.ArchivingDataObject

Schema Existence Validation

The stored procedure validates that the target archiving schema exists before attempting to create archive objects. If the schema does not exist, the operation fails with return code 6.

Archiving Execution Order

The @ArchivingExecutionOrder parameter allows you to control the sequence in which multiple data objects are archived. Lower values are processed first, enabling you to archive dependent objects in the correct order.

Archiving Logs

The table MetadataZoneCore.ArchivingLogMessage logs all actions performed during the archiving process:

Column	Description
RowsRead	Number of records found in the source data object
RowsArchived	Number of records moved from source to archive
RetentionPeriod	Threshold date for archiving (based on SYSUTCDATETIME() - RetentionPeriod)
RowsDeletedPhysical	Number of records physically deleted from archive
ArchivingRetentionPeriod	Threshold date for deletion (based on SYSUTCDATETIME() - ArchivingRetentionPeriod)
ArchivingStatusNumber	0 = Successful, 1 = Running, >1 = Error

Example to view all archiving logs:

SELECT *
FROM MetadataZoneCore.ArchivingLogMessage
ORDER BY InscriptionTimestamp DESC
;

Debug Mode

When @Debug = 1, the stored procedure provides verbose output including:

• Parameter values
• SQL statements being executed
• Intermediate validation results
• Final data object configuration

This is useful for troubleshooting and understanding the archiving configuration.

Examples

Example 1: Registration of a New Data Object for Archiving

Register a data object with default settings. The retention periods will be set by global variables configured in the ExMeX Core Framework.

DECLARE @ReturnProcessStatusNumber INT
EXEC @ReturnProcessStatusNumber = MetadataZoneCore.uspSetArchivingDataObject
     @SetArchivingExec          = N'A',
     @SourceSchemaName          = N'MetadataZone',
     @SourceDataObject          = N'ProcessStepVapourTrail',
     @ArchivingColumnCode       = N'ProcessEndDateTime',
     @IsActive                  = 1,
     @Comment                   = N'ExMeX Framework logging table'
SELECT @ReturnProcessStatusNumber AS [Return Status Number]
GO

Result:

• Source database is set to: current database
• Archiving schema is set to: MetadataZoneArchive (SourceSchemaName + 'Archive')
• Archiving data object is set to: ProcessStepVapourTrailArchive (SourceDataObject + 'Archive')
• Archiving data object is active
• Archive table is automatically created with clustered index on ProcessEndDateTime

Example 2: Registration in Another Database

Register a data object where both source and archive reside in a different database.

DECLARE @ReturnProcessStatusNumber INT
EXEC @ReturnProcessStatusNumber = MetadataZoneCore.uspSetArchivingDataObject
     @SetArchivingExec          = N'A',
     @SourceDatabaseName        = N'Playground',
     @SourceSchemaName          = N'dbo',
     @SourceDataObject          = N'Customer',
     @ArchivingColumnCode       = N'RegistrationYearPeriod',
     @ArchivingDatabaseName     = N'Playground',
     @ArchivingSchemaName       = N'dbo',
     @IsActive                  = 1,
     @Comment                   = N'Customer data archiving'
SELECT @ReturnProcessStatusNumber AS [Return Status Number]
GO

Result:

• Source database is set to: Playground
• Archiving database is set to: Playground
• Archiving schema is set to: dbo (same as source schema)
• Archiving data object is set to: CustomerArchive (SourceDataObject + 'Archive')
• Archiving data object is active

Example 3: Custom Archiving Schema

Register a data object with a custom archiving schema different from the default naming convention.

DECLARE @ReturnProcessStatusNumber INT
EXEC @ReturnProcessStatusNumber = MetadataZoneCore.uspSetArchivingDataObject
     @SetArchivingExec          = N'A',
     @SourceSchemaName          = N'MetadataZoneCore',
     @SourceDataObject          = N'BatchExecuteLoadProcessStepLog',
     @ArchivingColumnCode       = N'ModifiedAt',
     -- Default schema would have been 'MetadataZoneCoreArchive'
     @ArchivingSchemaName       = N'MetadataZoneArchive',
     @IsActive                  = 1,
     @Comment                   = N'ExMeX Framework module change logging table'
SELECT @ReturnProcessStatusNumber AS [Return Status Number]
GO

Result:

• Source database is set to: current database
• Archiving schema is set to: MetadataZoneArchive (custom schema)
• Archiving data object is set to: BatchExecuteLoadProcessStepLogArchive (SourceDataObject + 'Archive')
• Archiving data object is active

Example 4: Update Retention Periods

Update the retention periods for an existing archiving configuration.

DECLARE @ReturnProcessStatusNumber INT
EXEC @ReturnProcessStatusNumber = MetadataZoneCore.uspSetArchivingDataObject
     @SetArchivingExec          = N'U',
     @SourceSchemaName          = N'MetadataZoneCore',
     @SourceDataObject          = N'BatchExecuteLoadProcessStepLog',
     -- Updating retention period to a total retention period of 60 days
     @RetentionPeriod           = 30,
     @ArchivingRetentionPeriod  = 30,
     @Comment                   = N'Updated retention period'
SELECT @ReturnProcessStatusNumber AS [Return Status Number]
GO

Result:

Archiving configuration for the data object MetadataZoneCore.BatchExecuteLoadProcessStepLog is updated to:

• 30 days in source before archiving
• 30 days in archive before deletion
• Total retention period: 60 days

Example 5: Set Execution Order

Configure the execution order to ensure proper sequencing when multiple data objects are archived.

DECLARE @ReturnProcessStatusNumber INT
EXEC @ReturnProcessStatusNumber = MetadataZoneCore.uspSetArchivingDataObject
     @SetArchivingExec          = N'U',
     @SourceSchemaName          = N'MetadataZone',
     @SourceDataObject          = N'SatParallelAsyncLPSChunkLog',
     -- Set archiving execution order
     @ArchivingExecutionOrder   = 1,
     @Comment                   = N'Updated execution order - process first'
SELECT @ReturnProcessStatusNumber AS [Return Status Number]
GO

Result:

The archiving execution order for the MetadataZone.SatParallelAsyncLPSChunkLog data object is updated to 1 - it will be executed first before other archiving objects with higher order numbers.

Example 6: Deactivate Archiving

Temporarily disable archiving for a data object without deleting the configuration.

DECLARE @ReturnProcessStatusNumber INT
EXEC @ReturnProcessStatusNumber = MetadataZoneCore.uspSetArchivingDataObject
     @SetArchivingExec          = N'U',
     @SourceSchemaName          = N'MetadataZoneCore',
     @SourceDataObject          = N'BatchExecuteLoadProcessStepLog',
     -- Deactivate archiving of data object
     @IsActive                  = 0,
     @Comment                   = N'Temporarily disabled for maintenance'
SELECT @ReturnProcessStatusNumber AS [Return Status Number]
GO

Result:

Archiving configuration for the data object MetadataZoneCore.BatchExecuteLoadProcessStepLog is set to inactive. The archiving process will skip this object until reactivated.

Example 7: Update Archiving Column

Change the column used for archiving. Important: Create an index on the new column before updating.

Step 1: Create index on new column

CREATE NONCLUSTERED INDEX [IX_MetadataZoneCore_BatchExecuteLoadProcessStepLog_ModifiedAt]
    ON [MetadataZoneCore].[BatchExecuteLoadProcessStepLog] ([ModifiedAt] ASC)
    WITH (DATA_COMPRESSION = PAGE)

Step 2: Update archiving configuration

DECLARE @ReturnProcessStatusNumber INT
EXEC @ReturnProcessStatusNumber = MetadataZoneCore.uspSetArchivingDataObject
     @SetArchivingExec          = N'U',
     @SourceSchemaName          = N'MetadataZoneCore',
     @SourceDataObject          = N'BatchExecuteLoadProcessStepLog',
     -- Previous used column: AssertionTimestamp
     -- New column:
     @ArchivingColumnCode       = N'ModifiedAt',
     @Comment                   = N'Changed archiving column from AssertionTimestamp to ModifiedAt'
SELECT @ReturnProcessStatusNumber AS [Return Status Number]
GO

Result:

Archiving configuration for the data object MetadataZoneCore.BatchExecuteLoadProcessStepLog is updated to use the new archiving column ModifiedAt.

Example 8: Delete Archiving Configuration

Unregister a data object from the archiving framework. The archive table is NOT automatically dropped.

DECLARE @ReturnProcessStatusNumber INT
EXEC @ReturnProcessStatusNumber = MetadataZoneCore.uspSetArchivingDataObject
     @SetArchivingExec          = N'D',
     @SourceSchemaName          = N'MetadataZoneCore',
     @SourceDataObject          = N'BatchExecuteLoadProcessStepLog'
SELECT @ReturnProcessStatusNumber AS [Return Status Number]
GO

Result:

The archiving configuration for the data object MetadataZoneCore.BatchExecuteLoadProcessStepLog is unregistered from the framework.

Important: The stored procedure will output a SQL statement to manually drop the archive table if desired:

DROP TABLE [MetadataZoneArchive].[BatchExecuteLoadProcessStepLogArchive]

Caution: Only execute the DROP statement if you have made a backup. This operation cannot be reversed!

Example 9: Using Debug Mode

Execute with debug mode enabled to see detailed information about the operation.

DECLARE @ReturnProcessStatusNumber INT
EXEC @ReturnProcessStatusNumber = MetadataZoneCore.uspSetArchivingDataObject
     @SetArchivingExec          = N'A',
     @SourceSchemaName          = N'MetadataZone',
     @SourceDataObject          = N'TestDataObject',
     @ArchivingColumnCode       = N'CreatedDate',
     @IsActive                  = 0,
     @Debug                     = 1
SELECT @ReturnProcessStatusNumber AS [Return Status Number]
GO

Result:

The stored procedure will output detailed information including:

• All parameter values
• Validation results
• SQL statements being executed
• Final configuration details

This is particularly useful for troubleshooting archiving configurations.

See Also

• ExMeX Archiving Framework
• uspArchivingExecute
• uspConfigExMeXFramework
• Archiving Best Practices