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 @colSeperator NVARCHAR(20) 
SET			@colSeperator = ':,:'

DECLARE @MANameCurFlag  INTEGER 
SET 		@MANameCurFlag  = 0 

DECLARE @MACount 		INTEGER 
SET			@MACount		= 0

DECLARE @errorCode  INTEGER 
SET			@errorCode	= 0

DECLARE @retCode  INTEGER 
SET			@retCode	= 0

DECLARE @HostId 	INTEGER 
DECLARE @MAName 	NVARCHAR(510) 
DECLARE @SIDBStoreId	INTEGER 
DECLARE @PolicyName NVARCHAR(510) 
DECLARE @CopyName 	NVARCHAR(510) 

DECLARE @MAWithDDBVersionMismatch TABLE
(
		HostId	INTEGER,
		MAName		NVARCHAR(510),
		SIDBStoreId INTEGER
		PRIMARY KEY (SIDBStoreId)
)

PRINT @DescTag + ' Check for Deduplication databases (DDBs) with version mismatch'

INSERT INTO @MAWithDDBVersionMismatch
SELECT DISTINCT AC.Id, AC.name, T.SIDBStoreId
FROM IdxSIDBSubStore IDXSS WITH (NOLOCK)
    JOIN APP_Client AC WITH (NOLOCK) ON AC.id = IDXSS.ClientId
    JOIN   (SELECT ST.SIDBStoreId
                FROM IdxSIDBStore ST WITH (NOLOCK) 
                    JOIN IdxSIDBSubStore SS ON SS.SIDBStoreId = ST.SIDBStoreId
                    WHERE  ((SS.Version > ST.Version)
                            OR  ((SS.Flags & 8192 /*IDX_SIDBSTORE_FLAGS_MEMDB_DDB*/ <> 0)
                                AND  (ST.Flags & 8192 = 0)))
                            AND SS.firstBackupTime > 0
                            AND ST.SIDBStoreId > 0
            ) T ON IDXSS.SIDBStoreId = T.SIDBStoreId
ORDER BY T.SIDBStoreId


SELECT @errorCode	= @@ERROR, @MACount = @@ROWCOUNT
IF	@errorCode != 0 GOTO CX_EXIT 

--If no Media Agents found with DDB version mismatch
IF @MACount = 0 
BEGIN 
	SET @retCode = 0
	PRINT 'No MediaAgents have been found with DDB version mismatch.'
	GOTO CX_EXIT
END 

--If Media Agent(s) found with DDB version mismatch then just give warning in the Pre-Upgrade check
SET @retCode = 1
PRINT @WarningTag + ' Found one or more MediaAgents that have Deduplication databases (DDBs) with version mismatch.'

--Print Header
PRINT @HeaderTag + ' Media Agents:,:Policy Name:,:Copy Name'
PRINT '-----------------------------------------------------'
	
DECLARE	MANameCur CURSOR STATIC FOR 
SELECT HostId, MAName, AG.Name, AGC.Name
FROM @MAWithDDBVersionMismatch M, archGroupCopy AGC, archGroup AG
WHERE	M.SIDBStoreId = AGC.SIDBStoreId
		AND (AGC.DedupeFlags & 134217728) = 0 -- CVA_USE_GLOBAL_DEDUP_STORE_FLAG, Skip the copies associated with GDSP
		AND AGC.archGroupId = AG.id

SET	@errorCode	= @@ERROR 
IF	@errorCode != 0 GOTO CX_EXIT 

SET @MANameCurFlag = 1 
OPEN MANameCur 

SET	@errorCode	= @@ERROR 
IF	@errorCode != 0 GOTO CX_EXIT 

FETCH NEXT 
FROM  MANameCur 
INTO  @HostId, @MAName, @PolicyName, @CopyName 

SET	@errorCode	= @@ERROR 
IF	@errorCode != 0 GOTO CX_EXIT 

WHILE @@FETCH_STATUS = 0 
BEGIN 
	PRINT @DataTag + ' ' + @MAName + ':,:' + @PolicyName + ':,:' + @CopyName

	FETCH NEXT 
	FROM  MANameCur 
	INTO  @HostId, @MAName, @PolicyName, @CopyName 

	SET	@errorCode	=	@@ERROR 
	IF	@errorCode != 0 GOTO CX_EXIT 
END 

--Print Advice
PRINT @AdviceTag + ' If you upgrade while DDBs have version mismatches,'
								 + ' backups and auxcopies will not be able to run.'
								 + ' Convert the DDBs to get them to a consistent state.'

CX_EXIT: 

IF @MANameCurFlag = 1 
BEGIN 
	CLOSE	    MANameCur 
	DEALLOCATE	MANameCur 
END

IF @errorCode != 0
	SELECT 2
ELSE
	SELECT @retCode

RETURN