PIFAllSelectObjectList.SP.sql (Resource)

Element properties:

Source Code:

<Resource ID="PIFAllSelectObjectList.SP.sql" Accessibility="Internal" FileName="PIFAllSelectObjectList.SP.sql"/>

File Content: PIFAllSelectObjectList.SP.sql

IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_VirtualMachineManager_Report_MOMObjectListReportDataGet')


BEGIN


    EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_VirtualMachineManager_Report_MOMObjectListReportDataGet] AS RETURN 1')


END


GO





ALTER PROCEDURE [dbo].[Microsoft_SystemCenter_VirtualMachineManager_Report_MOMObjectListReportDataGet]


    @ObjectList xml,


    @LanguageCode varchar(3) = 'ENU'


AS


BEGIN


  SET NOCOUNT ON





  SELECT 


  DISTINCT


  vManagedEntity.ManagedEntityDefaultName, vManagedEntity.Path, ISNULL(vDisplayString.Name,vManagedEntityType.ManagedEntityTypeDefaultName)AS DisplayName, 


         vManagedEntity.ManagedEntityRowId, vManagementGroup.ManagementGroupDefaultName,


         ObjectList.ManagedEntity.value('@Use', 'nvarchar(255)') As ManagedEntityUse


  FROM   @ObjectList.nodes('/Data/Objects/Object') AS ObjectList(ManagedEntity) INNER JOIN


         vManagedEntity ON vManagedEntity.ManagedEntityRowId = ObjectList.ManagedEntity.value('.', 'int') INNER JOIN


         vManagedEntityType ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityType.ManagedEntityTypeRowId INNER JOIN


         vManagementGroup ON vManagedEntity.ManagementGroupRowId = vManagementGroup.ManagementGroupRowId LEFT OUTER JOIN


         vDisplayString ON vManagedEntityType.ManagedEntityTypeGuid = vDisplayString.ElementGuid AND 


         vDisplayString.LanguageCode = @LanguageCode





SET NOCOUNT OFF


END





GO


GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_VirtualMachineManager_Report_MOMObjectListReportDataGet] TO OpsMgrReader


GO