Using Metadata and code generation to programmatically set Validation Status ID’s  for all leaf members in an MDS Entity.

Primarily this script relies on the MDS mdm.udpMemberValidationStatusUpdate procedure for a single member. Unfortunately I could not get the stored proc for multiple members(mdm.udpMembersValidationStatusUpdate) to work, so I created a script to rely on the single member version.( mdm.udpMemberValidationStatusUpdate). I could not resolve the “operand type clash” error.

The script requires the model name and entity name, to generate a TSQL statement to update each member id to the designated Validation status.

IF OBJECT_ID('tempdb..#MemberIdList') IS NOT NULL
DROP TABLE #MemberIdList

DECLARE @ModelName nVarchar(50) = ‘Supplier’
DECLARE @Model_id int
DECLARE @Version_ID int
DECLARE @Entity_ID int
DECLARE @Entity_Name nVarchar(50) = ‘Supplier’
DECLARE @Entity_Table nVarchar(50)
DECLARE @sql nVarchar(500)
DECLARE @sqlExec nVarchar(500) = ‘EXEC mdm.udpMemberValidationStatusUpdate ‘
DECLARE @ValidationStatus_ID int = 4
— Found the following information intable [mdm].[tblList]
–ListCode ListName Seq ListOption
–lstValidationStatus ValidationStatus 0 New, Awaiting Validation
–lstValidationStatus ValidationStatus 1 Validating
–lstValidationStatus ValidationStatus 4 Validation Failed
–lstValidationStatus ValidationStatus 3 Validation Succeeded
–lstValidationStatus ValidationStatus 2 Awaiting Revalidation
–lstValidationStatus ValidationStatus 5 Awaiting Dependent Member Revalidation
–MemberType_ID = 1 (Leaf Member)
DECLARE @MemberType_ID int = 1
–Get Version id for Model
SET @Version_ID = (SELECT MAX(ID)
FROM mdm.viw_SYSTEM_SCHEMA_VERSION
WHERE Model_Name = @ModelName)
print @Version_ID
–Get Model IDfor Model
SET @Model_ID = (SELECT Model_ID
FROM mdm.viw_SYSTEM_SCHEMA_VERSION
WHERE Model_Name = @ModelName)
print @Model_ID
–Get Entity ID for specific Entity
SET @Entity_ID =
(SELECT [ID]
FROM [mdm].[tblEntity]
where [Model_ID] = @Model_ID
and [Name]= @Entity_Name)

–Get Entity Table Namefor specific Entity

SET @Entity_Table =
(SELECT[EntityTable]
FROM [mdm].[tblEntity]
where [Model_ID] = @Model_ID
and [Name]= @Entity_Name)
print ‘Processing Following Model ‘ + convert(varchar,@ModelName)
print ‘Model ID = ‘ + convert(varchar,@Model_id)
print ‘Version ID = ‘ + convert(varchar,@Version_ID)
print ‘Entity = ID ‘ + convert(varchar,@Entity_ID)
print ‘Entity Name = ‘ + convert(varchar,@Entity_Name)
print ‘Entity Table Name ‘ + convert(varchar,@Entity_Table)
print ‘Validation Status ID being set to ‘ + convert(varchar,@ValidationStatus_ID) + ‘ for all members in ‘+ convert(varchar,@Entity_Name)

–Create local temp table to hold member ids to update
CREATE TABLE #MemberIdList
( id int,
Processed int)
–Generate SQL to populaet temp table
set @sql = N’INSERT INTO #MemberIdList select id, 0 from mdm.’ +convert(varchar,@Entity_Table)

–Generate SQL to display stored proc update
EXECUTE sp_executesql @sql

–Create Tabe Variable to hold SQL Commands and prepare for execute
Declare @Id int
DECLARE @MemberSQL TABLE
(
ID int,
mdssql nvarchar(500),
Processed int
)
While (Select Count(*) From #MemberIdList Where Processed = 0) > 0
Begin
Select Top 1 @Id = ID From #MemberIdList Where Processed = 0

set @sql = N’ mdm.udpMemberValidationStatusUpdate ‘ +convert(varchar,@Version_ID) +’,’ +convert(varchar, @Entity_ID) +’,’ +convert(varchar,@ID) +’,’ +convert(varchar, @MemberType_ID) +’,’ + convert(varchar,@ValidationStatus_ID)

print @sql

INSERT INTO @MemberSQL
Select @id , @sql, 0

Update #MemberIdList Set Processed = 1 Where ID = @Id

End

Declare @sqlsyntax nVarchar(500)

While (Select Count(*) From @MemberSQL Where Processed = 0) > 0
Begin
Select Top 1 @id = id From @MemberSQL Where Processed = 0
Select Top 1 @sqlsyntax = mdssql From @MemberSQL Where Processed = 0

print @sqlsyntax
— comment this line to not execute update Validation Status ID
EXECUTE sp_executesql @sqlsyntax

Update @MemberSQL Set Processed = 1 Where ID = @Id
End

Declare @Entity_TableSQL nVarchar(500)
set @Entity_TableSQL = ‘SELECT * FROM [mdm].’ + @Entity_Table
print @Entity_TableSQL
exec sp_executesql @Entity_TableSQL
Here are several links I referenced:

The code for forcing the Validation of the Model is here. Jeremey Kashel’s Blog

Microsoft SQL Server 2012 Master Data Services 2/E Tyler Graham

Table Variables in TSQL

I am sure this can be improved, please contact me with questions or suggestions.

-Ira Warren Whiteside