IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_Visualization_Library_MultipleEntityContainmentGet' AND UID = SCHEMA_ID('SDK'))
BEGIN
EXECUTE ('CREATE PROCEDURE SDK.Microsoft_SystemCenter_Visualization_Library_MultipleEntityContainmentGet AS RETURN 1')
END
GO
IF NOT EXISTS (SELECT * FROM sys.types WHERE name = 'Microsoft_SystemCenter_Visualization_Library_ManagedEntityRowIdValuesTableType' AND schema_id = SCHEMA_ID('SDK'))
BEGIN
EXECUTE ('CREATE TYPE SDK.Microsoft_SystemCenter_Visualization_Library_ManagedEntityRowIdValuesTableType AS TABLE ( ManagedEntityRowId int )')
END
GO
ALTER PROCEDURE [sdk].[Microsoft_SystemCenter_Visualization_Library_MultipleEntityContainmentGet]
@ObjectList SDK.Microsoft_SystemCenter_Visualization_Library_ManagedEntityRowIdValuesTableType READONLY,
@ContainmentLevelCount int = 0,
@ContainmentStartLevel int = 0
AS
BEGIN
SET NOCOUNT ON
DECLARE @Error int
DECLARE @ExecError int
DECLARE @RowCount int
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
IF @RowCount > 0
BEGIN
DECLARE @ContainmentRelationshipTypeRowId int
SELECT @ContainmentRelationshipTypeRowId = RelationshipTypeRowId FROM vRelationshipType WHERE RelationshipTypeSystemName = 'System.Containment'
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT #RelationshipType(RelationshipTypeRowId, [Level])
SELECT RelationshipTypeRowId, [Level]
FROM dbo.RelationshipDerivedTypeHierarchy(@ContainmentRelationshipTypeRowId, 0)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
DECLARE @CurrentLevel int
SET @CurrentLevel = 1
WHILE (((@ContainmentLevelCount >= @CurrentLevel) OR (@ContainmentLevelCount = 0)) AND (@RowCount > 0))
BEGIN
INSERT INTO #ContainmentObjectList (ContainerManagedEntityRowId, ManagedEntityRowId, [Level])
SELECT DISTINCT me.ContainerManagedEntityRowId, r.TargetManagedEntityRowId, @CurrentLevel
FROM Relationship r
JOIN RelationshipManagementGroup rmg ON (r.RelationshipRowId = rmg.RelationshipRowId)
JOIN #RelationshipType rt ON (r.RelationshipTypeRowId = rt.RelationshipTypeRowId)
JOIN #ContainmentObjectList me ON (me.ManagedEntityRowId = r.SourceManagedEntityRowId) AND (me.[Level] = @CurrentLevel - 1)
WHERE (rmg.ToDateTime IS NULL)
IF @Error <> 0 OR @ExecError <> 0 GOTO QuitError
END
END
/* ------------------------------ */
SELECT ContainerManagedEntityRowId, ManagedEntityRowId
FROM #ContainmentObjectList
WHERE [Level] >= @ContainmentStartLevel
SET @Error = @@ERROR
QuitError:
DROP TABLE #ContainmentObjectList
DROP TABLE #RelationshipType
RETURN @Error
END
GO
GRANT EXECUTE ON [SDK].[Microsoft_SystemCenter_Visualization_Library_MultipleEntityContainmentGet] TO OpsMgrReader
GRANT EXECUTE ON TYPE::[SDK].[Microsoft_SystemCenter_Visualization_Library_ManagedEntityRowIdValuesTableType] TO OpsMgrReader
GO
Home