SET NOCOUNT ON 

DECLARE @ErrorTag	NVARCHAR(20)
DECLARE @WarningTag	NVARCHAR(20)
DECLARE @AdviceTag	NVARCHAR(20)
DECLARE @HeaderTag	NVARCHAR(20)
DECLARE @DataTag	NVARCHAR(20)
DECLARE @DescTag 	NVARCHAR(20)
DECLARE @colSeperator	NVARCHAR(20)
DECLARE @errorCode	INT
DECLARE @CSRelease	INT

SET @ErrorTag		= '###ERROR###'
SET @WarningTag		= '###WARNING###'
SET @AdviceTag		= '###ADVICE###'
SET @HeaderTag		= '###HEADER###'
SET @DataTag		= '###DATA###'
SET @DescTag		= '###DESCRIPTION###'
SET @colSeperator	= ':,:'
SET @errorCode		= 0
SET @CSRelease		= (select MAX (id) from SimAllGalaxyRel)

PRINT @DescTag + ' Check for SRM server package on CommServer'

DECLARE @blockedPkg	TABLE (t_id INT)


	INSERT INTO @blockedPkg
	SELECT	id
	FROM	SimInstalledPackages WITH (NOLOCK)
	WHERE	(clientId = 2 OR clientId in (select PMCLientId from APP_VMToPMMap where VMClientId = 2)) AND SimPackageId IN (401)
			-- Check for SRM NAS agent configured
			AND NOT EXISTS (SELECT	* 
							FROM	SimInstalledPackages S WITH (NOLOCK), APP_iDAName I WITH (NOLOCK), APP_IDAProp IP WITH (NOLOCK), APP_iDAName IProxy WITH (NOLOCK)
							WHERE	S.SimPackageId = 404
									AND	I.AppTypeId = 603 
									AND I.status & 2 = 0
									AND IP.componentNameId = I.id
									AND IP.attrName = 'SRM Proxy Client'
									AND IP.modified = 0
									AND S.clientId = CAST(IP.attrVal AS INT)
									AND IProxy.clientId = S.ClientId
									AND IProxy.appTypeId = 651
									AND IProxy.status & 2 = 0)
			-- Check for SRM Data Collection enabled on NAS NDMP Backup
			AND NOT EXISTS (SELECT	*
							FROM	APP_IDAName I WITH (NOLOCK), APP_IDAProp IP WITH (NOLOCK)
							WHERE	I.appTypeId = 13
									AND I.status & 2 = 0
									AND I.id = IP.componentNameId
									AND IP.attrName = 'SRM Data Collection Enabled'
									AND IP.modified = 0
									AND IP.attrVal = '1')

IF EXISTS (select * from @blockedPkg)
BEGIN
	SET @errorCode = 2
	PRINT  @ErrorTag + ' The SRM server package is installed on the CommServe computer. This package is deprecated and replaced with Build -Your-Own Reports.'
	PRINT  @AdviceTag + ' Please uninstall the SRM server package before upgrade. See the following documentation link for information on Build-Your-Own Reports http://documentation.commvault.com/commvault/release_10_0_0/books_online_1/english_us/features/reports/custom/transition_from_primary_storage_reports.htm'
END

SELECT @errorCode