<DataWarehouseScript ID="Microsoft.SystemCenter.OperationsManager.Reports.2007.Script.ReportDisplayNameListGet" Accessibility="Internal"> <Install>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_Library_GetReportDisplayNameList')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_GetReportDisplayNameList] AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_GetReportDisplayNameList]
@ObjectList xml,
@ListType Char(1),
@LanguageCode varchar(3) = 'ENU'
AS
BEGIN
SET NOCOUNT ON
If @ListType = '1'
--Management Group
BEGIN
Select Case When Mgp.ManagementGroupRowId%2=1 Then 1
Else 2
End As ColNumber,
Mgp.ManagementGroupDefaultName AS DisplayName
From @ObjectList.nodes('/*/Value') AS ObjectList(ManagementGroupGuid)
Inner Join vManagementGroup (NoLock) Mgp
On Mgp.ManagementGroupGuid = ObjectList.ManagementGroupGuid.value('.','uniqueidentifier')
Order By ColNumber, ManagementGroupDefaultName
END
If @ListType = '2'
--Management Pack
BEGIN
Select Case When Mp.ManagementPackRowId%2=1 Then 1
Else 2
End As ColNumber,
ISNULL(Ds.Name,Mp.ManagementPackDefaultName)AS DisplayName
From @ObjectList.nodes('/*/Value') AS ObjectList(ManagementPackRowId)
Inner Join vManagementPack (NoLock) Mp
On Mp.ManagementPackRowId = ObjectList.ManagementPackRowId.value('.','int')
Inner Join vDisplayString (NoLock) Ds
On Ds.ElementGuid = Mp.ManagementPackVersionIndependentGuid
And Ds.LanguageCode = @LanguageCode
Order By ColNumber, DisplayName
END
If @ListType = '3'
--Generic Options
BEGIN
Select OptList.[Option].value('./*[1]','int') as OptionValue,
OptList.[Option].value('./*[2]','varchar(200)') as OptionLabel
From @ObjectList.nodes('/Data/*/*') As OptList([Option])
Order By OptionValue
END
END
GO
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_GetReportDisplayNameList] TO OpsMgrReader
GO
</Install> <Uninstall>
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_Library_GetReportDisplayNameList')
BEGIN
DROP PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_GetReportDisplayNameList]
END
GO
</Uninstall> <Upgrade>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_Library_GetReportDisplayNameList')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_GetReportDisplayNameList] AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_GetReportDisplayNameList]
@ObjectList xml,
@ListType Char(1),
@LanguageCode varchar(3) = 'ENU'
AS
BEGIN
SET NOCOUNT ON
If @ListType = '1'
--Management Group
BEGIN
Select Case When Mgp.ManagementGroupRowId%2=1 Then 1
Else 2
End As ColNumber,
Mgp.ManagementGroupDefaultName AS DisplayName
From @ObjectList.nodes('/*/Value') AS ObjectList(ManagementGroupGuid)
Inner Join vManagementGroup (NoLock) Mgp
On Mgp.ManagementGroupGuid = ObjectList.ManagementGroupGuid.value('.','uniqueidentifier')
Order By ColNumber, ManagementGroupDefaultName
END
If @ListType = '2'
--Management Pack
BEGIN
Select Case When Mp.ManagementPackRowId%2=1 Then 1
Else 2
End As ColNumber,
ISNULL(Ds.Name,Mp.ManagementPackDefaultName)AS DisplayName
From @ObjectList.nodes('/*/Value') AS ObjectList(ManagementPackRowId)
Inner Join vManagementPack (NoLock) Mp
On Mp.ManagementPackRowId = ObjectList.ManagementPackRowId.value('.','int')
Inner Join vDisplayString (NoLock) Ds
On Ds.ElementGuid = Mp.ManagementPackVersionIndependentGuid
And Ds.LanguageCode = @LanguageCode
Order By ColNumber, DisplayName
END
If @ListType = '3'
--Generic Options
BEGIN
Select OptList.[Option].value('./*[1]','int') as OptionValue,
OptList.[Option].value('./*[2]','varchar(200)') as OptionLabel
From @ObjectList.nodes('/Data/*/*') As OptList([Option])
Order By OptionValue
END
END
GO
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_GetReportDisplayNameList] TO OpsMgrReader
GO