<DataWarehouseScript ID="Microsoft.SystemCenter.DataWarehouse.Script.MemberDatabase" Accessibility="Internal"> <Install>
IF NOT EXISTS (SELECT * FROM sysusers WHERE name = 'OpsMgrReader')
BEGIN
EXEC sp_addrole 'OpsMgrReader'
END
GO
IF NOT EXISTS (SELECT * FROM sysusers WHERE name = 'OpsMgrWriter')
BEGIN
EXEC sp_addrole 'OpsMgrWriter'
END
GO
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DomainTable' AND TABLE_SCHEMA = 'dbo')
BEGIN
CREATE TABLE dbo.DomainTable
(
DomainTableRowId int NOT NULL IDENTITY(1,1)
,TableObjectId int NOT NULL
,TableName sysname NOT NULL
,SchemaName sysname NOT NULL DEFAULT ('dbo')
,DatasetId uniqueidentifier NULL
,CreatedDateTime datetime NOT NULL DEFAULT (GETUTCDATE())
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DomainTableIndex' AND TABLE_SCHEMA = 'dbo')
BEGIN
CREATE TABLE dbo.DomainTableIndex
(
DomainTableIndexRowId int NOT NULL IDENTITY(1,1)
,DomainTableRowId int NOT NULL
,IndexId int NOT NULL
,IndexName sysname NOT NULL
,OptimizationFrequencyMinutes int NOT NULL
,LastConsideredForOptimizationDateTime datetime NOT NULL DEFAULT (GETUTCDATE())
,CreatedDateTime datetime NOT NULL DEFAULT (GETUTCDATE())
,RebuildFillFactor int NULL
,CONSTRAINT PK_DomainTableIndex PRIMARY KEY CLUSTERED (DomainTableIndexRowId)
,CONSTRAINT UN_DomainTableIndex UNIQUE (DomainTableRowId, IndexId)
)
END
GO
IF NOT EXISTS(SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('DomainTableIndex') AND name = 'RebuildFillFactor')
BEGIN
ALTER TABLE dbo.DomainTableIndex
ADD RebuildFillFactor int NULL
END
GO
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DomainTableIndexOptimizationHistory' AND TABLE_SCHEMA = 'dbo')
BEGIN
CREATE TABLE dbo.DomainTableIndexOptimizationHistory
(
DomainTableIndexOptimizationHistoryRowId int NOT NULL IDENTITY(1,1)
,DomainTableIndexRowId int NOT NULL
,OptimizationStartDateTime datetime NOT NULL DEFAULT (GETUTCDATE())
,OptimizationDurationSeconds int NOT NULL
,BeforeAvgFragmentationInPercent float NOT NULL
,AfterAvgFragmentationInPercent float NOT NULL
,OptimizationMethod varchar(50) NULL
,CONSTRAINT PK_DomainTableIndexOptimizationHistory PRIMARY KEY CLUSTERED (DomainTableIndexOptimizationHistoryRowId)
)
END
GO
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DomainTableStatisticsUpdateHistory' AND TABLE_SCHEMA = 'dbo')
BEGIN
CREATE TABLE dbo.DomainTableStatisticsUpdateHistory
(
DomainTableStatisticsUpdateHistoryRowId int NOT NULL IDENTITY(1,1)
,DomainTableRowId int NOT NULL
,StatisticName sysname NOT NULL
,UpdateStartDateTime datetime NOT NULL DEFAULT (GETUTCDATE())
,UpdateDurationSeconds int NOT NULL
,RowsSampledPercentage int NULL
,CONSTRAINT PK_DomainTableStatisticsUpdateHistory PRIMARY KEY CLUSTERED (DomainTableStatisticsUpdateHistoryRowId)
)
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'DiscoveryStandardDatasetGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.DiscoveryStandardDatasetGet AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.DiscoveryStandardDatasetGet
@ManagementGroupGuid uniqueidentifier
,@DatasetId uniqueidentifier
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ManagementGroupRowId int
,@Error int
,@OwnerManagementGroupInd bit
,@StandardDatasetInd bit
-- check management group is connected to this DW
DECLARE @CheckExecResult int
SELECT @OwnerManagementGroupInd = OwnerManagementGroupInd
FROM vManagementGroup
WHERE (ManagementGroupRowId = @ManagementGroupRowId)
IF (@OwnerManagementGroupInd = 1)
BEGIN
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'StandardDataset' AND TABLE_SCHEMA = 'dbo')
BEGIN
SELECT @StandardDatasetInd = 1
FROM StandardDataset
WHERE (DatasetId = @DatasetId)
END
SET @Error = @@ERROR
IF (@Error <> 0) GOTO Quit
END
ELSE
BEGIN
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'StandardDataset' AND TABLE_SCHEMA = 'dbo')
BEGIN
SELECT @StandardDatasetInd = 1
FROM StandardDataset sd
JOIN vManagementGroupDataset mgd ON (sd.DatasetId = mgd.DatasetId)
WHERE (sd.DatasetId = @DatasetId)
END
SET @Error = @@ERROR
IF (@Error <> 0) GOTO Quit
END
SELECT CAST(ISNULL(@StandardDatasetInd, 0) AS bit)
Quit:
END
GO
GRANT EXECUTE ON DiscoveryStandardDatasetGet TO OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'DomainTableIndexOptimize')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.DomainTableIndexOptimize AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.DomainTableIndexOptimize
@DatasetId uniqueidentifier = NULL
,@MinAvgFragmentationInPercentToOptimize int = 10
,@TargetFillfactor int = 100
,@BlockingMaintenanceStartTime char(5) = '01:00'
,@BlockingMaintenanceDurationMinutes int = 240
,@StatisticsUpdateMaxDurationSeconds int = 15
,@StatisticsUpdateMaxStatAgeHours int = 12
,@StatisticsSamplePercentage int = NULL
,@MinAvgFragmentationInPercentToReorg int = NULL
,@NumberOfIndexesToOptimize int = 1
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
SET @ErrorInd = 0
BEGIN TRY
DECLARE
@DomainTableIndexRowId int
,@IndexOptimizedInd bit
,@TableObjectId int
,@IndexId int
,@BeforeAvgFragmentationInPercent float
,@AfterAvgFragmentationInPercent float
,@LockResourceName sysname
,@ExecResult int
,@LockSetInd bit
,@TableName sysname
,@SchemaName sysname
,@IndexName sysname
,@OnlineStatement nvarchar(1000)
,@OfflineStatement nvarchar(1000)
,@ReorganizeStatement nvarchar(1000)
,@OnlineRebuildInd bit
,@IndexReorganized bit
,@OptimizationStartDateTime datetime
,@CanPerformBlockingOptimizationInd bit
,@NumberOfIndexesOptimized int
,@StatsUpdateProcessStartDateTime datetime
,@StatisticName sysname
,@StatsUpdateStartDateTime datetime
,@DomainTableRowId int
,@RebuildFillFactor int
-- set dataset domain table optimization lock
SELECT @LockResourceName =
CASE
WHEN @DatasetId IS NOT NULL THEN CAST(@DatasetId AS varchar(50)) + '_OptimizeDomain'
ELSE 'ConfigurationSpace'
END
IF (@ExecResult < -1)
BEGIN
RAISERROR(777971001, 16, 1, 'DomainTableOptimize', @ExecResult)
END
IF (@ExecResult = -1)
BEGIN
RETURN
END
SET @LockSetInd = 1
-- delete old optimization history records
DELETE DomainTableIndexOptimizationHistory
WHERE (OptimizationStartDateTime < DATEADD(day, -7, GETUTCDATE()))
-- fugure out if we can perform blocking maintenance
SET @CanPerformBlockingOptimizationInd =
CASE
WHEN (DATEDIFF(minute, CONVERT(char(8), GETDATE(), 112) + ' ' + @BlockingMaintenanceStartTime, GETDATE()) < @BlockingMaintenanceDurationMinutes)
THEN 1
ELSE 0
END
SET @IndexOptimizedInd = 0
SET @DomainTableIndexRowId = 0
SET @NumberOfIndexesOptimized = 0
WHILE ((@NumberOfIndexesToOptimize = 0) OR (@NumberOfIndexesToOptimize > @NumberOfIndexesOptimized))
AND (EXISTS (SELECT *
FROM DomainTableIndex i
JOIN DomainTable t ON (i.DomainTableRowId = t.DomainTableRowId)
WHERE (i.DomainTableIndexRowId > @DomainTableIndexRowId)
AND (i.LastConsideredForOptimizationDateTime < DATEADD(minute, -i.OptimizationFrequencyMinutes, GETUTCDATE()))
AND ((@DatasetId = t.DatasetId) OR ((@DatasetId IS NULL) AND (t.DatasetId IS NULL)))
)
)
BEGIN
SET @IndexOptimizedInd = 0
SET @IndexReorganized = 0
SET @OnlineRebuildInd = 0
SELECT TOP 1
@DomainTableIndexRowId = i.DomainTableIndexRowId
,@TableObjectId = t.TableObjectId
,@IndexId = i.IndexId
,@TableName = t.TableName
,@SchemaName = t.SchemaName
,@IndexName = i.IndexName
,@RebuildFillFactor = i.RebuildFillFactor
FROM DomainTableIndex i
JOIN DomainTable t ON (i.DomainTableRowId = t.DomainTableRowId)
WHERE (i.DomainTableIndexRowId > @DomainTableIndexRowId)
AND (i.LastConsideredForOptimizationDateTime < DATEADD(minute, -i.OptimizationFrequencyMinutes, GETUTCDATE()))
AND ((@DatasetId = t.DatasetId) OR ((@DatasetId IS NULL) AND (t.DatasetId IS NULL)))
ORDER BY i.DomainTableIndexRowId
-- check table / index exist and ids match
IF NOT EXISTS (SELECT *
FROM sys.tables t
JOIN sys.schemas s ON (s.Schema_id = t.schema_id)
JOIN sys.indexes i ON (t.object_id = i.object_id)
WHERE (t.name = @TableName)
AND (s.name = @SchemaName)
AND (t.object_id = @TableObjectId)
AND (i.name = @IndexName)
AND (i.index_id = @IndexId)
)
BEGIN
INSERT DomainTableIndexOptimizationHistory (
DomainTableIndexRowId
,OptimizationStartDateTime
,OptimizationDurationSeconds
,BeforeAvgFragmentationInPercent
,AfterAvgFragmentationInPercent
,OptimizationMethod
)
VALUES (
@DomainTableIndexRowId
,GETUTCDATE()
,0
,0
,0
,'none - tbl/ind not found or obj ids don''t match'
)
DELETE DomainTableIndex
WHERE (DomainTableIndexRowId = @DomainTableIndexRowId)
CONTINUE
END
IF NOT EXISTS (SELECT *
FROM sys.tables t
JOIN sys.schemas s ON (s.Schema_id = t.schema_id)
WHERE (t.name = @TableName)
AND (s.name = @SchemaName)
AND (t.object_id = @TableObjectId)
)
BEGIN
EXEC DomainTableUnregisterIndexOptimization @TableName = @TableName, @SchemaName = @SchemaName
END
SELECT @BeforeAvgFragmentationInPercent = avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), @TableObjectId, @IndexId, NULL, NULL)
WHERE alloc_unit_type_desc = 'IN_ROW_DATA'
-- If rebuild fillfactor is not defined in dbo.DomainTableIndex.[RebuildFillFactor], than take it from input parameter (by default 100%)
IF (@RebuildFillFactor IS NULL)
BEGIN
SET @RebuildFillFactor = @TargetFillFactor
END
IF (@BeforeAvgFragmentationInPercent >= @MinAvgFragmentationInPercentToOptimize) OR ((@MinAvgFragmentationInPercentToReorg IS NOT NULL) AND (@BeforeAvgFragmentationInPercent> = @MinAvgFragmentationInPercentToReorg))
BEGIN
SET @OnlineStatement = 'ALTER INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' REBUILD WITH (ONLINE=ON, FILLFACTOR=' + CAST(@RebuildFillFactor AS varchar) + ')'
SET @OfflineStatement = 'ALTER INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' REBUILD WITH (FILLFACTOR=' + CAST(@RebuildFillFactor AS varchar) + ')'
SET @ReorganizeStatement = 'ALTER INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' REORGANIZE'
SET @OptimizationStartDateTime = GETUTCDATE()
-- Check if a rebuild is necessary.
IF (@BeforeAvgFragmentationInPercent >= @MinAvgFragmentationInPercentToOptimize)
BEGIN
-- try online rebuild first
BEGIN TRY
EXECUTE (@OnlineStatement)
SET @IndexOptimizedInd = 1
SET @OnlineRebuildInd = 1
SET @NumberOfIndexesOptimized = @NumberOfIndexesOptimized + 1
END TRY
BEGIN CATCH
SET @OnlineRebuildInd = 0
END CATCH
-- do offline optimization only if online failed
-- and we are in allowed window
IF (@IndexOptimizedInd = 0) AND (@CanPerformBlockingOptimizationInd = 1)
BEGIN
EXECUTE (@OfflineStatement)
SET @IndexOptimizedInd = 1
SET @NumberOfIndexesOptimized = @NumberOfIndexesOptimized + 1
END
END
-- if we didn't rebuild the index, and reorganizing the index is an option, give it a try.
IF (@IndexOptimizedInd = 0) AND (@MinAvgFragmentationInPercentToReorg IS NOT NULL) AND (@BeforeAvgFragmentationInPercent> = @MinAvgFragmentationInPercentToReorg)
BEGIN
EXECUTE (@ReorganizeStatement)
SET @IndexOptimizedInd = 1
SET @IndexReorganized = 1
SET @NumberOfIndexesOptimized = @NumberOfIndexesOptimized + 1
END
SELECT @AfterAvgFragmentationInPercent = avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), @TableObjectId, @IndexId, NULL, NULL)
WHERE alloc_unit_type_desc = 'IN_ROW_DATA'
INSERT DomainTableIndexOptimizationHistory (
DomainTableIndexRowId
,OptimizationStartDateTime
,OptimizationDurationSeconds
,BeforeAvgFragmentationInPercent
,AfterAvgFragmentationInPercent
,OptimizationMethod
)
SELECT
@DomainTableIndexRowId
,@OptimizationStartDateTime
,ABS(DATEDIFF(second, @OptimizationStartDateTime, GETUTCDATE()))
,@BeforeAvgFragmentationInPercent
,@AfterAvgFragmentationInPercent
,CASE @IndexOptimizedInd
WHEN 0 THEN 'none - blocking optimization not allowed'
ELSE CASE
WHEN @OnlineRebuildInd = 1 THEN 'online rebuild'
WHEN @IndexReorganized = 1 THEN 'online reorganize'
ELSE 'offline rebuild'
END
END
END
-- mark table as "considered" and move on
UPDATE DomainTableIndex
SET LastConsideredForOptimizationDateTime = GETUTCDATE()
WHERE (DomainTableIndexRowId = @DomainTableIndexRowId)
END
-- delete old stats update history records
DELETE DomainTableStatisticsUpdateHistory
WHERE (UpdateStartDateTime < DATEADD(day, -7, GETUTCDATE()))
-- consider stats update if no index was optimized
IF (@NumberOfIndexesOptimized = 0)
BEGIN
SET @StatsUpdateProcessStartDateTime = GETUTCDATE()
WHILE (DATEADD(second, -@StatisticsUpdateMaxDurationSeconds, GETUTCDATE()) < @StatsUpdateProcessStartDateTime)
BEGIN
SET @TableName = NULL
-- nolock hint below is to prevent this statement from deadlocking with DROP TABLE
-- statement run by stage processing stored procedures
SELECT TOP 1
@DomainTableRowId = d.DomainTableRowId
,@TableName = d.TableName
,@SchemaName = d.SchemaName
,@StatisticName = s.name
FROM sys.stats s WITH (NOLOCK)
JOIN sys.objects o WITH (NOLOCK) ON (s.object_id = o.object_id)
JOIN DomainTable d on (d.TableObjectId = o.object_id)
AND ((@DatasetId = d.DatasetId) OR ((@DatasetId IS NULL) AND (d.DatasetId IS NULL)))
WHERE (s.auto_created = 0)
AND (s.no_recompute = 0)
AND (STATS_DATE(s.object_id, s.stats_id) < DATEADD(hour, -@StatisticsUpdateMaxStatAgeHours, GETDATE()))
ORDER BY 1 ASC
IF (@TableName IS NULL)
BEGIN
BREAK
END
ELSE
BEGIN
SET @OnlineStatement = 'UPDATE STATISTICS ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' ' + QUOTENAME(@StatisticName)
IF (@StatisticsSamplePercentage >= 100)
SET @OnlineStatement = @OnlineStatement + ' WITH FULLSCAN'
ELSE IF (@StatisticsSamplePercentage > 0)
SET @OnlineStatement = @OnlineStatement + ' WITH SAMPLE ' + CAST(@StatisticsSamplePercentage AS varchar(10)) + ' PERCENT'
SET @StatsUpdateStartDateTime = GETUTCDATE()
EXECUTE (@OnlineStatement)
INSERT DomainTableStatisticsUpdateHistory (
DomainTableRowId
,StatisticName
,UpdateStartDateTime
,UpdateDurationSeconds
,RowsSampledPercentage
)
VALUES
(
@DomainTableRowId
,@StatisticName
,@StatsUpdateStartDateTime
,ABS(DATEDIFF(second, @StatsUpdateStartDateTime, GETUTCDATE()))
,@StatisticsSamplePercentage
)
END
END
END
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'DomainTableRegisterIndexOptimization')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.DomainTableRegisterIndexOptimization AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.DomainTableRegisterIndexOptimization
@TableName sysname
,@SchemaName sysname = 'dbo'
,@IndexOptimizationFrequencyMinutes int = 240
,@DatasetId uniqueidentifier = NULL
,@IncludeClusteredIndex bit = 1
,@RebuildFillFactor int = NULL
AS
BEGIN
SET NOCOUNT ON
DECLARE @Error int
DECLARE @DomainTableRowId int
DECLARE @Trancount int
DECLARE @TableObjectId int
SET @Trancount = @@TRANCOUNT
BEGIN TRAN
SELECT @Error = @@ERROR
IF @Error <> 0 GOTO Quit
SELECT @Error = @@ERROR
IF @Error <> 0 GOTO Quit
DELETE i
FROM DomainTableIndex i
JOIN DomainTable t WITH (UPDLOCK) ON (i.DomainTableRowId = t.DomainTableRowId)
WHERE (t.TableObjectId = @TableObjectId)
SELECT @Error = @@ERROR
IF @Error <> 0 GOTO Quit
DELETE DomainTable
WHERE (TableObjectId = @TableObjectId)
SELECT @Error = @@ERROR
IF @Error <> 0 GOTO Quit
SELECT @Error = @@ERROR
IF @Error <> 0 GOTO Quit
INSERT DomainTableIndex (
DomainTableRowId
,IndexId
,IndexName
,OptimizationFrequencyMinutes
,RebuildFillFactor
)
SELECT
@DomainTableRowId
,sysindexes.indid
,sysindexes.[name]
,@IndexOptimizationFrequencyMinutes
,@RebuildFillFactor
FROM sysindexes
WHERE (id = @TableObjectId)
AND (indid > (SELECT CASE @IncludeClusteredIndex WHEN 0 THEN 1 ELSE 0 END))
SELECT @Error = @@ERROR
IF @Error <> 0 GOTO Quit
COMMIT
Quit:
IF (@@TRANCOUNT > @Trancount)
ROLLBACK
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'DomainTableUnregisterIndexOptimization')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.DomainTableUnregisterIndexOptimization AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.DomainTableUnregisterIndexOptimization
@TableName sysname
,@SchemaName sysname = 'dbo'
AS
BEGIN
SET NOCOUNT ON
DECLARE @Error int
DECLARE @Trancount int
DECLARE @TableObjectId int
DECLARE @DomainTableRowId int
SET @Trancount = @@TRANCOUNT
BEGIN TRAN
SELECT @Error = @@ERROR
IF @Error <> 0 GOTO Quit
SELECT @Error = @@ERROR
IF @Error <> 0 GOTO Quit
IF (@TableObjectId IS NOT NULL)
BEGIN
SELECT @DomainTableRowId = DomainTableRowId
FROM DomainTable
WHERE (TableObjectId = @TableObjectId)
SELECT @Error = @@ERROR
IF @Error <> 0 GOTO Quit
END
ELSE
BEGIN
SELECT @DomainTableRowId = DomainTableRowId
FROM DomainTable
WHERE (TableName = @TableName)
AND (SchemaName = @SchemaName)
SELECT @Error = @@ERROR
IF @Error <> 0 GOTO Quit
END
DELETE DomainTableIndex
WHERE (DomainTableRowId = @DomainTableRowId)
SELECT @Error = @@ERROR
IF @Error <> 0 GOTO Quit
DELETE DomainTable
WHERE (DomainTableRowId = @DomainTableRowId)
SELECT @Error = @@ERROR
IF @Error <> 0 GOTO Quit
COMMIT
Quit:
IF (@@TRANCOUNT > @Trancount)
ROLLBACK
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ManagementGroupAccessCheck')
BEGIN
EXECUTE ('CREATE PROCEDURE ManagementGroupAccessCheck AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.ManagementGroupAccessCheck
@ManagementGroupGuid uniqueidentifier
,@ManagementGroupRowId int OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE @ManagementGroupGuidString varchar(100)
SET @ManagementGroupGuidString = CAST(@ManagementGroupGuid AS varchar(100))
DECLARE @WriterLoginName nvarchar(256)
SET @ManagementGroupRowId = NULL
-- check management group is connected to this DW
SELECT @ManagementGroupRowId = ManagementGroupRowId
,@WriterLoginName = WriterLoginName
FROM vManagementGroup
WHERE (ManagementGroupGuid = @ManagementGroupGuid)
AND (ConnectRejectedInd = 0)
AND (ConnectConfirmedDateTime IS NOT NULL)
IF (@ManagementGroupRowId IS NULL)
BEGIN
RAISERROR(777970007, 16, 1, @ManagementGroupGuidString)
RETURN -1
END
IF (@WriterLoginName <> SUSER_NAME())
BEGIN
SET @ManagementGroupRowId = NULL
DECLARE @UserName nvarchar(256)
SET @UserName = SUSER_NAME()
RAISERROR(777970008, 16, 1, @ManagementGroupGuidString, @UserName)
RETURN -1
END
RETURN 0
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'RelatedManagedEntityList')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.RelatedManagedEntityList AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.RelatedManagedEntityList
@ManagedEntityRowIdXml xml
,@RelationshipTypeRowId int
,@StartDateTime datetime = '20000101'
,@EndDateTime datetime = '99991231'
,@IncludeDerivedRelationshipTypesInd bit = 1
AS
BEGIN
SET NOCOUNT ON
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
SET @ErrorInd = 0
BEGIN TRY
CREATE TABLE #RelationshipType (
RelationshipTypeRowId int
,[Level] int
)
SELECT DISTINCT r.TargetManagedEntityRowId
FROM Relationship r
JOIN RelationshipManagementGroup rmg ON (r.RelationshipRowId = rmg.RelationshipRowId)
JOIN #RelationshipType rt ON (r.RelationshipTypeRowId = rt.RelationshipTypeRowId)
JOIN (SELECT ManagedEntityRowId = ME.ManagedEntityRowId.value('.', 'int')
FROM @ManagedEntityRowIdXml.nodes('Root/ManagedEntityRowId') AS ME(ManagedEntityRowId)
) AS me ON (me.ManagedEntityRowId = r.SourceManagedEntityRowId)
WHERE (rmg.FromDateTime <= @EndDateTime) AND (ISNULL(rmg.ToDateTime, '99991231') >= @StartDateTime)
DROP TABLE #RelationshipType
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
-- report error if any
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
GRANT EXECUTE ON dbo.RelatedManagedEntityList TO OpsMgrReader, OpsMgrWriter
GO
-- this permission allows dynamic view querying to see table fragmentation
GRANT VIEW DATABASE STATE TO OpsMgrWriter
GO
-- change schema alter to allow domain table index rebuild
GRANT ALTER ON SCHEMA::dbo TO OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vAggregationType')
BEGIN
EXECUTE ('CREATE VIEW vAggregationType AS SELECT A = 1')
END
GO
ALTER VIEW vAggregationType
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vAggregationType
GO
GRANT SELECT ON vAggregationType TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vAlertCustomField')
BEGIN
EXECUTE ('CREATE VIEW vAlertCustomField AS SELECT A = 1')
END
GO
ALTER VIEW vAlertCustomField
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vAlertCustomField
GO
GRANT SELECT ON vAlertCustomField TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vDataset')
BEGIN
EXECUTE ('CREATE VIEW vDataset AS SELECT A = 1')
END
GO
ALTER VIEW vDataset
AS
SELECT
DatasetId
,DatasetTypeManagementPackVersionRowId
,MemberDatabaseRowId
,DatasetDefaultName
,DatasetDefaultDescription
,ConfigurationXml = CAST (ConfigurationXml AS xml)
,InstallCompletedInd
,InstalledDateTime
,LastUpgradeCompletedInd
,LastUpgradedDateTime
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vDatasetNoXml
GO
GRANT SELECT ON vDataset TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vDatasetType')
BEGIN
EXECUTE ('CREATE VIEW vDatasetType AS SELECT A = 1')
END
GO
ALTER VIEW vDatasetType
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vDatasetType
GO
GRANT SELECT ON vDatasetType TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vDatasetTypeManagementPackVersion')
BEGIN
EXECUTE ('CREATE VIEW vDatasetTypeManagementPackVersion AS SELECT A = 1')
END
GO
ALTER VIEW vDatasetTypeManagementPackVersion
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vDatasetTypeManagementPackVersion
GO
GRANT SELECT ON vDatasetTypeManagementPackVersion TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vDatasetTypeSchemaTypeManagementPackVersion')
BEGIN
EXECUTE ('CREATE VIEW vDatasetTypeSchemaTypeManagementPackVersion AS SELECT A = 1')
END
GO
ALTER VIEW vDatasetTypeSchemaTypeManagementPackVersion
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vDatasetTypeSchemaTypeManagementPackVersion
GO
GRANT SELECT ON vDatasetTypeSchemaTypeManagementPackVersion TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vDate')
BEGIN
EXECUTE ('CREATE VIEW vDate AS SELECT A = 1')
END
GO
ALTER VIEW vDate
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vDate
GO
GRANT SELECT ON vDate TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vDateTime')
BEGIN
EXECUTE ('CREATE VIEW vDateTime AS SELECT A = 1')
END
GO
ALTER VIEW vDateTime
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vDateTime
GO
GRANT SELECT ON vDateTime TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vDiagnostic')
BEGIN
EXECUTE ('CREATE VIEW vDiagnostic AS SELECT A = 1')
END
GO
ALTER VIEW vDiagnostic
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vDiagnostic
GO
GRANT SELECT ON vDiagnostic TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vDiagnosticManagementPackVersion')
BEGIN
EXECUTE ('CREATE VIEW vDiagnosticManagementPackVersion AS SELECT A = 1')
END
GO
ALTER VIEW vDiagnosticManagementPackVersion
AS
SELECT
DiagnosticManagementPackVersionRowId
,DiagnosticRowId
,ManagementPackVersionRowId
,WorkflowCategoryRowId
,Accessibility
,EnabledInd
,TargetManagedEntityTypeRowId
,MonitorRowId
,ExecuteOnHealthStateRowId
,RemotableInd
,Timeout
,DefinitionXml = CAST (DefinitionXml AS xml)
,DefinitionHash
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vDiagnosticManagementPackVersionNoXml
GO
GRANT SELECT ON vDiagnosticManagementPackVersion TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vDiscovery')
BEGIN
EXECUTE ('CREATE VIEW vDiscovery AS SELECT A = 1')
END
GO
ALTER VIEW vDiscovery
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vDiscovery
GO
GRANT SELECT ON vDiscovery TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vDiscoveryManagementPackVersion')
BEGIN
EXECUTE ('CREATE VIEW vDiscoveryManagementPackVersion AS SELECT A = 1')
END
GO
ALTER VIEW vDiscoveryManagementPackVersion
AS
SELECT
DiscoveryManagementPackVersionRowId
,DiscoveryRowId
,ManagementPackVersionRowId
,WorkflowCategoryRowId
,TargetManagedEntityTypeRowId
,EnabledInd
,ConfirmDeliveryInd
,RemotableInd
,Priority
,DefinitionXml = CAST (DefinitionXml AS xml)
,DefinitionHash
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vDiscoveryManagementPackVersionNoXml
GO
GRANT SELECT ON vDiscoveryManagementPackVersion TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vDisplayString')
BEGIN
EXECUTE ('CREATE VIEW vDisplayString AS SELECT A = 1')
END
GO
ALTER VIEW vDisplayString
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vDisplayString
GO
GRANT SELECT ON vDisplayString TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vHealthServiceOutage')
BEGIN
EXECUTE ('CREATE VIEW vHealthServiceOutage AS SELECT A = 1')
END
GO
ALTER VIEW vHealthServiceOutage
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vHealthServiceOutage
GO
GRANT SELECT ON vHealthServiceOutage TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vHealthState')
BEGIN
EXECUTE ('CREATE VIEW vHealthState AS SELECT A = 1')
END
GO
ALTER VIEW vHealthState
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vHealthState
GO
GRANT SELECT ON vHealthState TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vImage')
BEGIN
EXECUTE ('CREATE VIEW vImage AS SELECT A = 1')
END
GO
ALTER VIEW vImage
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vImage
GO
GRANT SELECT ON vImage TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vImageReference')
BEGIN
EXECUTE ('CREATE VIEW vImageReference AS SELECT A = 1')
END
GO
ALTER VIEW vImageReference
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vImageReference
GO
GRANT SELECT ON vImageReference TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vMaintenanceMode')
BEGIN
EXECUTE ('CREATE VIEW vMaintenanceMode AS SELECT A = 1')
END
GO
ALTER VIEW vMaintenanceMode
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vMaintenanceMode
GO
GRANT SELECT ON vMaintenanceMode TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vMaintenanceModeHistory')
BEGIN
EXECUTE ('CREATE VIEW vMaintenanceModeHistory AS SELECT A = 1')
END
GO
ALTER VIEW vMaintenanceModeHistory
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vMaintenanceModeHistory
GO
GRANT SELECT ON vMaintenanceModeHistory TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vManagedEntity')
BEGIN
EXECUTE ('CREATE VIEW vManagedEntity AS SELECT A = 1')
END
GO
ALTER VIEW vManagedEntity
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vManagedEntity
GO
GRANT SELECT ON vManagedEntity TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vManagedEntityManagementGroup')
BEGIN
EXECUTE ('CREATE VIEW vManagedEntityManagementGroup AS SELECT A = 1')
END
GO
ALTER VIEW vManagedEntityManagementGroup
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vManagedEntityManagementGroup
GO
GRANT SELECT ON vManagedEntityManagementGroup TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vManagedEntityProperty')
BEGIN
EXECUTE ('CREATE VIEW vManagedEntityProperty AS SELECT A = 1')
END
GO
ALTER VIEW vManagedEntityProperty
AS
SELECT
ManagedEntityPropertyRowId
,ManagedEntityRowId
,PropertyXml = CAST (PropertyXml AS xml)
,DeltaXml = CAST (DeltaXml AS xml)
,FromDateTime
,ToDateTime
,DWCreatedDateTime
,DWLastModifiedDateTime
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vManagedEntityPropertyNoXml
GO
GRANT SELECT ON vManagedEntityProperty TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vManagedEntityPropertyChange')
BEGIN
EXECUTE ('CREATE VIEW vManagedEntityPropertyChange AS SELECT A = 1')
END
GO
ALTER VIEW vManagedEntityPropertyChange
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vManagedEntityPropertyChange
GO
GRANT SELECT ON vManagedEntityPropertyChange TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vManagedEntityPropertySet')
BEGIN
EXECUTE ('CREATE VIEW vManagedEntityPropertySet AS SELECT A = 1')
END
GO
ALTER VIEW vManagedEntityPropertySet
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vManagedEntityPropertySet
GO
GRANT SELECT ON vManagedEntityPropertySet TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vManagedEntityType')
BEGIN
EXECUTE ('CREATE VIEW vManagedEntityType AS SELECT A = 1')
END
GO
ALTER VIEW vManagedEntityType
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vManagedEntityType
GO
GRANT SELECT ON vManagedEntityType TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vManagedEntityTypeImage')
BEGIN
EXECUTE ('CREATE VIEW vManagedEntityTypeImage AS SELECT A = 1')
END
GO
ALTER VIEW vManagedEntityTypeImage
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vManagedEntityTypeImage
GO
GRANT SELECT ON vManagedEntityTypeImage TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vManagedEntityTypeManagementPackVersion')
BEGIN
EXECUTE ('CREATE VIEW vManagedEntityTypeManagementPackVersion AS SELECT A = 1')
END
GO
ALTER VIEW vManagedEntityTypeManagementPackVersion
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vManagedEntityTypeManagementPackVersion
GO
GRANT SELECT ON vManagedEntityTypeManagementPackVersion TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vManagedEntityTypeProperty')
BEGIN
EXECUTE ('CREATE VIEW vManagedEntityTypeProperty AS SELECT A = 1')
END
GO
ALTER VIEW vManagedEntityTypeProperty
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vManagedEntityTypeProperty
GO
GRANT SELECT ON vManagedEntityTypeProperty TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vManagedEntityTypePropertyManagementPackVersion')
BEGIN
EXECUTE ('CREATE VIEW vManagedEntityTypePropertyManagementPackVersion AS SELECT A = 1')
END
GO
ALTER VIEW vManagedEntityTypePropertyManagementPackVersion
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vManagedEntityTypePropertyManagementPackVersion
GO
GRANT SELECT ON vManagedEntityTypePropertyManagementPackVersion TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vManagementGroup')
BEGIN
EXECUTE ('CREATE VIEW vManagementGroup AS SELECT A = 1')
END
GO
ALTER VIEW vManagementGroup
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vManagementGroup
GO
GRANT SELECT ON vManagementGroup TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vManagementGroupDataset')
BEGIN
EXECUTE ('CREATE VIEW vManagementGroupDataset AS SELECT A = 1')
END
GO
ALTER VIEW vManagementGroupDataset
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vManagementGroupDataset
GO
GRANT SELECT ON vManagementGroupDataset TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vManagementGroupManagementPackVersion')
BEGIN
EXECUTE ('CREATE VIEW vManagementGroupManagementPackVersion AS SELECT A = 1')
END
GO
ALTER VIEW vManagementGroupManagementPackVersion
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vManagementGroupManagementPackVersion
GO
GRANT SELECT ON vManagementGroupManagementPackVersion TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vManagementPack')
BEGIN
EXECUTE ('CREATE VIEW vManagementPack AS SELECT A = 1')
END
GO
ALTER VIEW vManagementPack
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vManagementPack
GO
GRANT SELECT ON vManagementPack TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vManagementPackVersion')
BEGIN
EXECUTE ('CREATE VIEW vManagementPackVersion AS SELECT A = 1')
END
GO
ALTER VIEW vManagementPackVersion
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vManagementPackVersion
GO
GRANT SELECT ON vManagementPackVersion TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vManagementPackVersionReference')
BEGIN
EXECUTE ('CREATE VIEW vManagementPackVersionReference AS SELECT A = 1')
END
GO
ALTER VIEW vManagementPackVersionReference
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vManagementPackVersionReference
GO
GRANT SELECT ON vManagementPackVersionReference TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vMemberDatabase')
BEGIN
EXECUTE ('CREATE VIEW vMemberDatabase AS SELECT A = 1')
END
GO
ALTER VIEW vMemberDatabase
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vMemberDatabase
GO
GRANT SELECT ON vMemberDatabase TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vMemberDatabaseScript')
BEGIN
EXECUTE ('CREATE VIEW vMemberDatabaseScript AS SELECT A = 1')
END
GO
ALTER VIEW vMemberDatabaseScript
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vMemberDatabaseScript
GO
GRANT SELECT ON vMemberDatabaseScript TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vMonitor')
BEGIN
EXECUTE ('CREATE VIEW vMonitor AS SELECT A = 1')
END
GO
ALTER VIEW vMonitor
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vMonitor
GO
GRANT SELECT ON vMonitor TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vMonitorManagementPackVersion')
BEGIN
EXECUTE ('CREATE VIEW vMonitorManagementPackVersion AS SELECT A = 1')
END
GO
ALTER VIEW vMonitorManagementPackVersion
AS
SELECT
MonitorManagementPackVersionRowId
,MonitorRowId
,ManagementPackVersionRowId
,WorkflowCategoryRowId
,ParentMonitorRowId
,UnitMonitorInd
,AggregateMonitorInd
,DependencyMonitorInd
,TargetManagedEntityTypeRowId
,RelationshipTypeRowId
,DependencyMonitorRowId
,Accessibility
,EnabledInd
,RemotableInd
,ConfirmDeliveryInd
,GenerateAlertInd
,AlertOnHealthStateRowId
,AutoResolveAlertInd
,AlertPriority
,AlertSeverity
,Algorithm
,AlgorithmParameter
,Priority
,MemberInMaintenanceHealthStateRowId
,MemberUnavailableHealthStateRowId
,DefinitionXml = CAST (DefinitionXml AS xml)
,DefinitionHash
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vMonitorManagementPackVersionNoXml
GO
GRANT SELECT ON vMonitorManagementPackVersion TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vOverride')
BEGIN
EXECUTE ('CREATE VIEW vOverride AS SELECT A = 1')
END
GO
ALTER VIEW vOverride
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vOverride
GO
GRANT SELECT ON vOverride TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vOverrideManagementPackVersion')
BEGIN
EXECUTE ('CREATE VIEW vOverrideManagementPackVersion AS SELECT A = 1')
END
GO
ALTER VIEW vOverrideManagementPackVersion
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vOverrideManagementPackVersion
GO
GRANT SELECT ON vOverrideManagementPackVersion TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vRecovery')
BEGIN
EXECUTE ('CREATE VIEW vRecovery AS SELECT A = 1')
END
GO
ALTER VIEW vRecovery
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vRecovery
GO
GRANT SELECT ON vRecovery TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vRecoveryManagementPackVersion')
BEGIN
EXECUTE ('CREATE VIEW vRecoveryManagementPackVersion AS SELECT A = 1')
END
GO
ALTER VIEW vRecoveryManagementPackVersion
AS
SELECT
RecoveryManagementPackVersionRowId
,RecoveryRowId
,ManagementPackVersionRowId
,WorkflowCategoryRowId
,Accessibility
,EnabledInd
,TargetManagedEntityTypeRowId
,RemotableInd
,Timeout
,MonitorRowId
,ExecuteOnHealthStateRowId
,ExecuteOnDiagnosticRowId
,ResetMonitorInd
,DefinitionXml = CAST (DefinitionXml AS xml)
,DefinitionHash
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vRecoveryManagementPackVersionNoXml
GO
GRANT SELECT ON vRecoveryManagementPackVersion TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vRelationship')
BEGIN
EXECUTE ('CREATE VIEW vRelationship AS SELECT A = 1')
END
GO
ALTER VIEW vRelationship
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vRelationship
GO
GRANT SELECT ON vRelationship TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vRelationshipManagementGroup')
BEGIN
EXECUTE ('CREATE VIEW vRelationshipManagementGroup AS SELECT A = 1')
END
GO
ALTER VIEW vRelationshipManagementGroup
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vRelationshipManagementGroup
GO
GRANT SELECT ON vRelationshipManagementGroup TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vRelationshipProperty')
BEGIN
EXECUTE ('CREATE VIEW vRelationshipProperty AS SELECT A = 1')
END
GO
ALTER VIEW vRelationshipProperty
AS
SELECT
RelationshipPropertyRowId
,RelationshipRowId
,PropertyXml = CAST (PropertyXml AS xml)
,DeltaXml = CAST (DeltaXml AS xml)
,FromDateTime
,ToDateTime
,DWCreatedDateTime
,DWLastModifiedDateTime
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vRelationshipPropertyNoXml
GO
GRANT SELECT ON vRelationshipProperty TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vRelationshipType')
BEGIN
EXECUTE ('CREATE VIEW vRelationshipType AS SELECT A = 1')
END
GO
ALTER VIEW vRelationshipType
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vRelationshipType
GO
GRANT SELECT ON vRelationshipType TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vRelationshipTypeManagementPackVersion')
BEGIN
EXECUTE ('CREATE VIEW vRelationshipTypeManagementPackVersion AS SELECT A = 1')
END
GO
ALTER VIEW vRelationshipTypeManagementPackVersion
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vRelationshipTypeManagementPackVersion
GO
GRANT SELECT ON vRelationshipTypeManagementPackVersion TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vRelationshipTypeProperty')
BEGIN
EXECUTE ('CREATE VIEW vRelationshipTypeProperty AS SELECT A = 1')
END
GO
ALTER VIEW vRelationshipTypeProperty
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vRelationshipTypeProperty
GO
GRANT SELECT ON vRelationshipTypeProperty TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vRelationshipTypePropertyManagementPackVersion')
BEGIN
EXECUTE ('CREATE VIEW vRelationshipTypePropertyManagementPackVersion AS SELECT A = 1')
END
GO
ALTER VIEW vRelationshipTypePropertyManagementPackVersion
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vRelationshipTypePropertyManagementPackVersion
GO
GRANT SELECT ON vRelationshipTypePropertyManagementPackVersion TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vReport')
BEGIN
EXECUTE ('CREATE VIEW vReport AS SELECT A = 1')
END
GO
ALTER VIEW vReport
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vReport
GO
GRANT SELECT ON vReport TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vReportDisplayString')
BEGIN
EXECUTE ('CREATE VIEW vReportDisplayString AS SELECT A = 1')
END
GO
ALTER VIEW vReportDisplayString
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vReportDisplayString
GO
GRANT SELECT ON vReportDisplayString TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vReportManagementPackVersion')
BEGIN
EXECUTE ('CREATE VIEW vReportManagementPackVersion AS SELECT A = 1')
END
GO
ALTER VIEW vReportManagementPackVersion
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vReportManagementPackVersion
GO
GRANT SELECT ON vReportManagementPackVersion TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vResolutionState')
BEGIN
EXECUTE ('CREATE VIEW vResolutionState AS SELECT A = 1')
END
GO
ALTER VIEW vResolutionState
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vResolutionState
GO
GRANT SELECT ON vResolutionState TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vRule')
BEGIN
EXECUTE ('CREATE VIEW vRule AS SELECT A = 1')
END
GO
ALTER VIEW vRule
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vRule
GO
GRANT SELECT ON vRule TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vRuleManagementPackVersion')
BEGIN
EXECUTE ('CREATE VIEW vRuleManagementPackVersion AS SELECT A = 1')
END
GO
ALTER VIEW vRuleManagementPackVersion
AS
SELECT
RuleManagementPackVersionRowId
,RuleRowId
,ManagementPackVersionRowId
,WorkflowCategoryRowId
,TargetManagedEntityTypeRowId
,EnabledInd
,ConfirmDeliveryInd
,RemotableInd
,Priority
,DefinitionXml = CAST (DefinitionXml AS xml)
,DefinitionHash
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vRuleManagementPackVersionNoXml
GO
GRANT SELECT ON vRuleManagementPackVersion TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vSchemaType')
BEGIN
EXECUTE ('CREATE VIEW vSchemaType AS SELECT A = 1')
END
GO
ALTER VIEW vSchemaType
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vSchemaType
GO
GRANT SELECT ON vSchemaType TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vSchemaTypeManagementPackVersion')
BEGIN
EXECUTE ('CREATE VIEW vSchemaTypeManagementPackVersion AS SELECT A = 1')
END
GO
ALTER VIEW vSchemaTypeManagementPackVersion
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vSchemaTypeManagementPackVersion
GO
GRANT SELECT ON vSchemaTypeManagementPackVersion TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vScript')
BEGIN
EXECUTE ('CREATE VIEW vScript AS SELECT A = 1')
END
GO
ALTER VIEW vScript
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vScript
GO
GRANT SELECT ON vScript TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vScriptManagementPackVersion')
BEGIN
EXECUTE ('CREATE VIEW vScriptManagementPackVersion AS SELECT A = 1')
END
GO
ALTER VIEW vScriptManagementPackVersion
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vScriptManagementPackVersion
GO
GRANT SELECT ON vScriptManagementPackVersion TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vStringResource')
BEGIN
EXECUTE ('CREATE VIEW vStringResource AS SELECT A = 1')
END
GO
ALTER VIEW vStringResource
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vStringResource
GO
GRANT SELECT ON vStringResource TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vTime')
BEGIN
EXECUTE ('CREATE VIEW vTime AS SELECT A = 1')
END
GO
ALTER VIEW vTime
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vTime
GO
GRANT SELECT ON vTime TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vTimeZone')
BEGIN
EXECUTE ('CREATE VIEW vTimeZone AS SELECT A = 1')
END
GO
ALTER VIEW vTimeZone
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vTimeZone
GO
GRANT SELECT ON vTimeZone TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vTypedManagedEntity')
BEGIN
EXECUTE ('CREATE VIEW vTypedManagedEntity AS SELECT A = 1')
END
GO
ALTER VIEW vTypedManagedEntity
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vTypedManagedEntity
GO
GRANT SELECT ON vTypedManagedEntity TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vWorkflowCategory')
BEGIN
EXECUTE ('CREATE VIEW vWorkflowCategory AS SELECT A = 1')
END
GO
ALTER VIEW vWorkflowCategory
AS
SELECT *
FROM $Config/MainServerNameWithTrailingDot$$Config/MainDatabaseNameWithTrailingDot$dbo.vWorkflowCategory
GO
GRANT SELECT ON vWorkflowCategory TO OpsMgrWriter, OpsMgrReader
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'ManagedEntityDerivedTypeHierarchy') and xtype in (N'FN', N'IF', N'TF'))
EXECUTE('CREATE FUNCTION ManagedEntityDerivedTypeHierarchy() RETURNS @A TABLE (a int) AS BEGIN RETURN END')
GO
ALTER FUNCTION ManagedEntityDerivedTypeHierarchy
(
@ManagedEntityTypeRowId int
,@LevelCount int = 0
)
RETURNS @ResultTable TABLE
(
[Level] int NOT NULL
,ManagedEntityTypeRowId int NOT NULL
)
AS
BEGIN
DECLARE
@CurrentLevel int
,@RowCount int
WHILE (((@LevelCount >= @CurrentLevel) OR (@LevelCount = 0)) AND (@RowCount > 0))
BEGIN
INSERT @ResultTable ([Level], ManagedEntityTypeRowId)
SELECT DISTINCT @CurrentLevel, metmpv.ManagedEntityTypeRowId
FROM vManagedEntityTypeManagementPackVersion metmpv
JOIN @ResultTable rt ON (metmpv.BaseManagedEntityTypeRowId = rt.ManagedEntityTypeRowId)
WHERE (rt.[Level] = @CurrentLevel - 1)
-- if type is derived from a given type on multiple levels according to
-- different version of MPs (which can happen on upgrade) take only the lowest level
AND (NOT EXISTS (SELECT * FROM @ResultTable r WHERE r.ManagedEntityTypeRowId = metmpv.ManagedEntityTypeRowId))
SET @RowCount = @@ROWCOUNT
SET @CurrentLevel = @CurrentLevel + 1
END
RETURN
END
GO
GRANT SELECT ON ManagedEntityDerivedTypeHierarchy TO OpsMgrReader, OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'ManagedEntityBaseTypeHierarchy') and xtype in (N'FN', N'IF', N'TF'))
EXECUTE('CREATE FUNCTION ManagedEntityBaseTypeHierarchy() RETURNS @A TABLE (a int) AS BEGIN RETURN END')
GO
ALTER FUNCTION ManagedEntityBaseTypeHierarchy
(
@ManagedEntityTypeRowId int
,@LevelCount int = 0
)
RETURNS @ResultTable TABLE
(
[Level] int NOT NULL
,ManagedEntityTypeRowId int NOT NULL
)
AS
BEGIN
DECLARE
@CurrentLevel int
,@RowCount int
WHILE (((@LevelCount >= @CurrentLevel) OR (@LevelCount = 0)) AND (@RowCount > 0))
BEGIN
INSERT @ResultTable ([Level], ManagedEntityTypeRowId)
SELECT DISTINCT @CurrentLevel, BaseManagedEntityTypeRowId
FROM vManagedEntityTypeManagementPackVersion metmpv
JOIN @ResultTable rt ON (metmpv.ManagedEntityTypeRowId = rt.ManagedEntityTypeRowId)
WHERE (metmpv.BaseManagedEntityTypeRowId IS NOT NULL)
AND (rt.[Level] = @CurrentLevel - 1)
-- if type is derived from a given type on multiple levels according to
-- different version of MPs (which can happen on upgrade) take only the lowest level
AND (NOT EXISTS (SELECT * FROM @ResultTable r WHERE r.ManagedEntityTypeRowId = metmpv.BaseManagedEntityTypeRowId))
SET @RowCount = @@ROWCOUNT
SET @CurrentLevel = @CurrentLevel + 1
END
RETURN
END
GO
GRANT SELECT ON ManagedEntityBaseTypeHierarchy TO OpsMgrReader, OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'RelationshipDerivedTypeHierarchy') and xtype in (N'FN', N'IF', N'TF'))
EXECUTE('CREATE FUNCTION RelationshipDerivedTypeHierarchy() RETURNS @A TABLE (a int) AS BEGIN RETURN END')
GO
ALTER FUNCTION RelationshipDerivedTypeHierarchy
(
@RelationshipTypeRowId int
,@LevelCount int = 0
)
RETURNS @ResultTable TABLE
(
[Level] int NOT NULL
,RelationshipTypeRowId int NOT NULL
)
AS
BEGIN
DECLARE
@CurrentLevel int
,@RowCount int
WHILE (((@LevelCount >= @CurrentLevel) OR (@LevelCount = 0)) AND (@RowCount > 0))
BEGIN
INSERT @ResultTable ([Level], RelationshipTypeRowId)
SELECT DISTINCT @CurrentLevel, rtmpv.RelationshipTypeRowId
FROM vRelationshipTypeManagementPackVersion rtmpv
JOIN @ResultTable rt ON (rtmpv.BaseRelationshipTypeRowId = rt.RelationshipTypeRowId)
WHERE (rt.[Level] = @CurrentLevel - 1)
-- if relationship type is derived from a given type on multiple levels according to
-- different version of MPs (which can happen on upgrade - check System.Containment)
-- take only the lowest level
AND (NOT EXISTS (SELECT * FROM @ResultTable r WHERE r.RelationshipTypeRowId = rtmpv.RelationshipTypeRowId))
SET @RowCount = @@ROWCOUNT
SET @CurrentLevel = @CurrentLevel + 1
END
RETURN
END
GO
GRANT SELECT ON RelationshipDerivedTypeHierarchy TO OpsMgrReader, OpsMgrWriter
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'RelationshipBaseTypeHierarchy') and xtype in (N'FN', N'IF', N'TF'))
EXECUTE('CREATE FUNCTION RelationshipBaseTypeHierarchy() RETURNS @A TABLE (a int) AS BEGIN RETURN END')
GO
ALTER FUNCTION RelationshipBaseTypeHierarchy
(
@RelationshipTypeRowId int
,@LevelCount int = 0
)
RETURNS @ResultTable TABLE
(
[Level] int NOT NULL
,RelationshipTypeRowId int NOT NULL
)
AS
BEGIN
DECLARE
@CurrentLevel int
,@RowCount int
WHILE (((@LevelCount >= @CurrentLevel) OR (@LevelCount = 0)) AND (@RowCount > 0))
BEGIN
INSERT @ResultTable ([Level], RelationshipTypeRowId)
SELECT DISTINCT @CurrentLevel, BaseRelationshipTypeRowId
FROM vRelationshipTypeManagementPackVersion rtmpv
JOIN @ResultTable rt ON (rtmpv.RelationshipTypeRowId = rt.RelationshipTypeRowId)
WHERE (rtmpv.BaseRelationshipTypeRowId IS NOT NULL)
AND (rt.[Level] = @CurrentLevel - 1)
-- if relationship type is derived from a given type on multiple levels according to
-- different version of MPs (which can happen on upgrade - check System.Containment)
-- take only the lowest level
AND (NOT EXISTS (SELECT * FROM @ResultTable r WHERE r.RelationshipTypeRowId = rtmpv.BaseRelationshipTypeRowId))
SET @RowCount = @@ROWCOUNT
SET @CurrentLevel = @CurrentLevel + 1
END
RETURN
END
GO
GRANT SELECT ON RelationshipBaseTypeHierarchy TO OpsMgrReader, OpsMgrWriter
GO
Home
ENU