Purging production record data for a specific Production Record ID

6min

Use Cases
It might be necessary for users to remove an individual production record if it for example used the wrong triggers or other circumstance make the data unusable or invalid.
User can purge production record data for a specific production record.
User can do this either by using applications such as:
Microsoft SQL Server Management Studio
MSSQL CLI
This can also be used for building custom solutions based on applications which allow for an ODBC connection such as:
Excel
C#
.Net
Python
To obtain a production record ID to purge, several options do exist such as explained in this document:
﻿Reading Production Record Event Data with UTC timestamp﻿﻿
Method
Execute Stored Procedure PROREC_Misc_ProcessData_Purge_Records_by_RecordID
Input Variables
Variable name
Datatype
Description
Example
@StartRecordID
BIGINT
This is either the single Production Record ID to be purged or the first of a range of Production Record ID's.
﻿
Mandatory!
Can not be NULL or Empty
124
@EndRecordID
BIGINT
This is the optional last Production Record ID if a range of Production Records has to be purge.
NULL
@Node
VARCHAR(50)
The optional name of the root Node and the name of the data model.

This is optional and mostly used for purging a range of production record ID's for a specific data model.

For a single Production Record ID, this value is not needed.
NULL
﻿
﻿
﻿
﻿
﻿
How to use
To purge a single Production Record ID or a range of Production Record ID's, call the stored procedure PROREC_Misc_ProcessData_Purge_Records_by_RecordID using the T-SQL EXEC or EXECUTE keyword and provide the Input variables.
Below is an example using T-SQL in SQL Server Management Studio:
EXECUTE [LE_ProductionRecord].[dbo].[PROREC_Misc_ProcessData_Purge_Records_by_RecordID]
@StartRecordID = 124,
@EndRecordID = 130,
@Node = N'Tooling_Shop_Status';
Note: The system will always create a backup first before purging. This way, the production record data can always be restored.
If an output window does exist, the progress can be observed.
production record data purge for a range of prorec id's
﻿
Result
The stored procedure will verify that the provided values are correct and data exist. Then it will create the backup before purging the production record data. 
The result can be read back from the log table PROREC_Misc_Log.
Below is an example using T-SQL in SQL Server Management Studio:
SELECT TOP (100) [LogID]
,[LogType]
,[LogProcedure]
,[LogEntry]
,[LogTime]
FROM [LE_ProductionRecord].[dbo].[PROREC_Misc_Log]
WHERE [LogProcedure] = 'PROREC_Misc_ProcessData_Purge_Records_by_RecordID'
Order by LogID Desc;
With the result returned
log entry for prorec id purge
﻿
﻿

Variable name	Datatype	Description	Example
@StartRecordID	BIGINT	This is either the single Production Record ID to be purged or the first of a range of Production Record ID's. Mandatory! Can not be NULL or Empty	124
@EndRecordID	BIGINT	This is the optional last Production Record ID if a range of Production Records has to be purge.	NULL
@Node	VARCHAR(50)	The optional name of the root Node and the name of the data model. This is optional and mostly used for purging a range of production record ID's for a specific data model. For a single Production Record ID, this value is not needed.	NULL

Updated 29 Sep 2023

Purging production record data for a specific data model

Restore production record data