SET NOCOUNT ON

DECLARE @ErrorTag NVARCHAR(20) 
SET			@ErrorTag = '###ERROR###'

DECLARE @WarningTag NVARCHAR(20) 
SET			@WarningTag = '###WARNING###'

DECLARE @AdviceTag NVARCHAR(20) 
SET			@AdviceTag = '###ADVICE###'

DECLARE @HeaderTag NVARCHAR(20) 
SET			@HeaderTag = '###HEADER###'

DECLARE @DataTag NVARCHAR(20) 
SET			@DataTag = '###DATA###'

DECLARE @DescTag NVARCHAR(20) 
SET	@DescTag = '###DESCRIPTION###'

DECLARE @errorCode  INTEGER 
SET 		@errorCode = 0

PRINT @DescTag + ' Check for migrated storage policies which have backups from the current CommCell.'

IF EXISTS (	SELECT 1 FROM archGroup AG WITH (NOLOCK), 
					APP_Application APP WITH (NOLOCK) , 
					JMBkpStats JM WITH (NOLOCK)
			WHERE ( AG.id = APP.dataArchGrpID OR AG.id = APP.logArchGrpID ) 
					AND AG.flags & 262144 = 262144
					AND APP.id = JM.appId
					AND JM.commCellId = 2 )
BEGIN
	DECLARE @spList varchar(1024) = ''
	SELECT  @spList = @spList + SP.name + + ', '   
	FROM	(SELECT DISTINCT AG.name FROM archGroup AG WITH (NOLOCK), 
			APP_Application APP WITH (NOLOCK) , 
			JMBkpStats JM WITH (NOLOCK)
	WHERE ( AG.id = APP.dataArchGrpID OR AG.id = APP.logArchGrpID ) 
		AND AG.flags & 262144 = 262144 --CVA_SP_CCM_OR_GRC_MIGRATED_FLAG
		AND APP.id = JM.appId
		AND JM.commCellId = 2) AS SP
	SET @spList = LEFT(@spList, LEN(@spList)-1)
	
	PRINT @WarningTag  + ' There are some backup jobs ran to the migrated storage policies [' + @spList + '] .' 
	PRINT @AdviceTag + ' Please re-associate all the subclients poining to these migrated storage policies [' + @spList + '] to by creating new or different storage policies.' 
		
	SET @errorCode = 1

	GOTO ERROR_EXIT
END

ERROR_EXIT:
	IF @errorCode != 0 
		SELECT 1
	ELSE
	BEGIN
		PRINT 'Check for migrated storage policies which have backups from the current CommCell is succeeded.' 
		SELECT 0
	END

RETURN