USE [mdmDB]
GO
DECLARE @ModelName NVARCHAR(50) = 'ModelName'
DECLARE @UserName NVARCHAR(50) = SUSER_NAME()
DECLARE @UserId INT = (SELECT ID FROM mdm.tblUser u WHERE u.UserName = @UserName)
DECLARE @VersionId INT = (SELECT MAX(ID) FROM mdm.viw_SYSTEM_SCHEMA_VERSION WHERE Model_Name = @ModelName)
INSERT INTO mdmDB.mdm.tblStgMemberAttribute
(ModelName
, EntityName
, MemberType_ID
, MemberCode
, AttributeName
, AttributeValue)
SELECT DISTINCT
@ModelName AS [ModelName]
,
N'Entity Name' AS [EntityName]
,1
,m.Code --code to temp table
,'MDMMemberStatus'
,'De-Activated'
FROM [dwScratch].[dbo].[temptable] s --temp table where target members are saved
INNER JOIN mdmDB.mdm.view m --entity view in MDM
ON s.Code = m.[Code]
EXEC mdm.udpStagingSweep @UserId=@UserId, @VersionId=@VersionId, @Process=1 -- process or batch updates in the staging table
-- If long-running or stucked staging batch with Master Data Services in SQL Server 2008 R2. The issue happens most frequently when a fairly large batch of rows are being processed for update or deletion.
-- Run the following scripts
--Set the MDS database in single user mode and rollback uncommitted transactions.
ALTER DATABASE [MDS] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
--Re-enable Service Broker on the MDS database.
ALTER DATABASE [MDS] SET ENABLE_BROKER WITH NO_WAIT;
--Set the MDS database back in to regular multi-user mode.
ALTER DATABASE [MDS] SET MULTI_USER WITH ROLLBACK IMMEDIATE;
GO
DECLARE @ModelName NVARCHAR(50) = 'ModelName'
DECLARE @UserName NVARCHAR(50) = SUSER_NAME()
DECLARE @UserId INT = (SELECT ID FROM mdm.tblUser u WHERE u.UserName = @UserName)
DECLARE @VersionId INT = (SELECT MAX(ID) FROM mdm.viw_SYSTEM_SCHEMA_VERSION WHERE Model_Name = @ModelName)
INSERT INTO mdmDB.mdm.tblStgMemberAttribute
(ModelName
, EntityName
, MemberType_ID
, MemberCode
, AttributeName
, AttributeValue)
SELECT DISTINCT
@ModelName AS [ModelName]
,
N'Entity Name' AS [EntityName]
,1
,m.Code --code to temp table
,'MDMMemberStatus'
,'De-Activated'
FROM [dwScratch].[dbo].[temptable] s --temp table where target members are saved
INNER JOIN mdmDB.mdm.view m --entity view in MDM
ON s.Code = m.[Code]
EXEC mdm.udpStagingSweep @UserId=@UserId, @VersionId=@VersionId, @Process=1 -- process or batch updates in the staging table
-- If long-running or stucked staging batch with Master Data Services in SQL Server 2008 R2. The issue happens most frequently when a fairly large batch of rows are being processed for update or deletion.
-- Run the following scripts
--Set the MDS database in single user mode and rollback uncommitted transactions.
ALTER DATABASE [MDS] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
--Re-enable Service Broker on the MDS database.
ALTER DATABASE [MDS] SET ENABLE_BROKER WITH NO_WAIT;
--Set the MDS database back in to regular multi-user mode.
ALTER DATABASE [MDS] SET MULTI_USER WITH ROLLBACK IMMEDIATE;