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 @NRSClientNameCurFlag  INTEGER 
SET 		@NRSClientNameCurFlag  = 0 

DECLARE @NRSClientCount 		INTEGER 
SET			@NRSClientCount		= 0

DECLARE @errorCode  INTEGER 
SET			@errorCode	= 0

DECLARE @retCode  INTEGER 
SET			@retCode	= 0

DECLARE @HostId 	INTEGER 
DECLARE @NRSClientName 	NVARCHAR(510) 
DECLARE @output		NVARCHAR(MAX)

DECLARE @NRSClientAssociatedToDedupeCopies TABLE
(
		HostId		INTEGER,
		NRSClientName		NVARCHAR(510) 
		PRIMARY KEY (HostId)
)


PRINT @DescTag + ' Check for NDMP clients associated to Deduplication enabled storage policies'

INSERT	INTO @NRSClientAssociatedToDedupeCopies
SELECT	DISTINCT AC.id, AC.name
FROM	APP_Client AC WITH (NOLOCK), MMNdmpHostInfo N WITH (NOLOCK)
WHERE	AC.id = N.ClientId 
		AND AC.releaseID = 14 
		AND AC.simoperatingSystemId = 29 /*SIM_ID_BLUEARC_FILER_OS*/

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

DELETE  @NRSClientAssociatedToDedupeCopies
FROM	@NRSClientAssociatedToDedupeCopies N
WHERE	NOT EXISTS (SELECT	* 
					FROM	archGroupCopy AGC WITH (NOLOCK), archGroup AG WITH (NOLOCK), APP_Application A WITH (NOLOCK)
					WHERE	AG.id = A.dataArchGrpID
							AND (AGC.archGroupId = AG.id OR (AG.incrSP <> 0 AND AGC.archGroupId = AG.incrSP))
							AND AGC.dedupeFlags & 262144  > 0
							AND N.HostId = A.clientID)

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


SELECT	@NRSClientCount = COUNT(*)
FROM	@NRSClientAssociatedToDedupeCopies

--If No clients with NDMP Remote Server installed are associated with Deduplication enabled Storage Policy
IF @NRSClientCount = 0 
BEGIN 
	SET @retCode = 0
	PRINT 'No clients with NDMP Remote Server installed are associated with Deduplication enabled storage policies.'
	GOTO CX_EXIT
END 

--Print Error/Warning
SET @retCode = 1
SET @output = @WarningTag + ' The following (' + CAST(@NRSClientCount AS VARCHAR(10)) + ') clients with NDMP Remote Server are associated to a Deduplication enabled storage policy.' + CHAR(10) 
SET @output = @output + 'After the client upgrade, there will be a new baseline created on the disk library which will require more storage space.' + CHAR(10)
SET @output = @output + CHAR(10)

PRINT @output

--Print Header
PRINT @HeaderTag + ' Clients'
PRINT '----------------------------'
	
DECLARE	NRSClientNameCur CURSOR STATIC FOR 
SELECT HostId, NRSClientName
FROM @NRSClientAssociatedToDedupeCopies

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

SET @NRSClientNameCurFlag = 1 
OPEN NRSClientNameCur 

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

FETCH NEXT 
FROM  NRSClientNameCur 
INTO  @HostId, @NRSClientName 

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

WHILE @@FETCH_STATUS = 0 
BEGIN 
	PRINT @DataTag + ' ' + @NRSClientName

	FETCH NEXT 
	FROM  NRSClientNameCur 
	INTO  @HostId, @NRSClientName 

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

--Print Advice
SET @output = CHAR(10) + @AdviceTag + ' Check the baseline size required to run backups for these clients.'
SET @output = @output + CHAR(10)

PRINT @output

CX_EXIT: 

IF @NRSClientNameCurFlag = 1 
BEGIN 
	CLOSE	    NRSClientNameCur 
	DEALLOCATE	NRSClientNameCur 
END

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

RETURN