SET NOCOUNT ON

DECLARE @ErrorTag   nvarchar(20) = '###ERROR###';
DECLARE @WarningTag nvarchar(20) = '###WARNING###';
DECLARE @AdviceTag  nvarchar(20) = '###ADVICE###';
DECLARE @HeaderTag  nvarchar(20) = '###HEADER###';
DECLARE @DataTag    nvarchar(20) = '###DATA###';
DECLARE @DescTag    nvarchar(20) = '###DESCRIPTION###';

PRINT @DescTag + ' Confirm absence of shared index cache';

DECLARE @maSharedIndex nvarchar(max);
DECLARE @maUncPath     nvarchar(max);
DECLARE @maIIC         nvarchar(max);
DECLARE @maICS         nvarchar(max);
DECLARE @maLongPath    nvarchar(max);

SELECT
@maSharedIndex = CASE WHEN IP.IsShared > 0              THEN COALESCE(@maSharedIndex + ', ', '') + C.name ELSE @maSharedIndex END,
@maUncPath     = CASE WHEN IA.Path LIKE '\\%'           THEN COALESCE(@maUncPath     + ', ', '') + C.name ELSE @maUncPath     END,
@maIIC         = CASE WHEN IA.Flags & 1 <> 0            THEN COALESCE(@maIIC         + ', ', '') + C.name ELSE @maIIC         END,
@maICS         = CASE WHEN IC.CatalogServerClientId > 0 THEN COALESCE(@maICS         + ', ', '') + C.name ELSE @maICS         END,
@maLongPath    = CASE WHEN LEN(IA.Path) > 75            THEN COALESCE(@maLongPath    + ', ', '') + C.name ELSE @maLongPath    END
FROM APP_Client C WITH (NOLOCK), IdxAccessPath IA WITH (NOLOCK), IdxCache IC WITH (NOLOCK), IdxPool IP WITH (NOLOCK)
WHERE C.id = IA.clientId AND IA.IdxCacheId = IC.IdxCacheId AND IC.IdxPoolId = IP.IdxPoolId
AND C.id > 1 AND IC.IdxCacheType = 1;

DECLARE @errorCode int = 0;

-- shared index
IF @maSharedIndex <> ''
BEGIN
    PRINT @ErrorTag  + ' Shared index cache support is deprecated.';
    PRINT @AdviceTag + ' Move all index cache directories on [' + @maSharedIndex + '] to local drives and resume the upgrade.';
    SET @errorCode = 1;
END

-- UNC index
IF @maUncPath <> ''
BEGIN
    PRINT @ErrorTag  + ' Support of UNC path for index cache is deprecated.';
    PRINT @AdviceTag + ' Move all index cache directories on [' + @maUncPath + '] to local drives and resume the upgrade.';
    SET @errorCode = 1;
END

-- intermediate index
IF @maIIC <> ''
BEGIN
    PRINT @ErrorTag  + ' Support of Intermediate index cache is deprecated.';
    PRINT @AdviceTag + ' Move all index cache directories on [' + @maIIC + '] to local drives and resume the upgrade.';
    SET @errorCode = 1;
END

-- catalog server
IF @maICS <> ''
BEGIN
    PRINT @ErrorTag  + ' Support of ICS is deprecated.';
    PRINT @AdviceTag + ' Move all index cache directories on [' + @maICS + '] to local drives, disable ICS and then resume the upgrade.';
    SET @errorCode = 1;
END

-- long index path
IF @maLongPath <> ''
BEGIN
       PRINT @ErrorTag  + ' Index path should be less than 75 characters long.';
       PRINT @AdviceTag + ' Change the index cache on [' + @maLongPath + '] to a directory path shorter than 75 characters.';
       SET @errorCode = 1;
END

ERROR_EXIT:
    IF @errorCode <> 0
        SELECT 2;
    ELSE
    BEGIN
        PRINT 'Check for absence of shared index cache succeeded.';
        SELECT 0;
    END

RETURN