

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/Db/TblScript/NTUpdateTableDataToTextData.script] ---------- 


SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON

-- ALLOW_COMMENTS_BELOW_HERE:

-- ----------------------------------------------------------------------

--                                                                      

--           Copyright (c) 1998  CommVault Systems, Inc.                

--                  All rights reserved.                                

--                                                                      

--                                                                   

--        This is unpublished proprietary source code of CommVault  

--        Systems, Inc. The copyright notice above does not evidence 

--        any actual or intended publication of such source code. 

-- ----------------------------------------------------------------------*/

-- rcsid[]="@(#)$Source: /cvs/cvsrepro/GX/vaultcx/Source/CommServer/Db/TblScript/NTUpdateTableDataToTextData.script,v $ $Id: NTUpdateTableDataToTextData.script,v 1.3.8.2 2021/06/11 23:41:05 snandhini Exp $";

DECLARE @updateAlertTokenGUID VARCHAR(40) = '245F65AE-834C-4438-80C8-F3E6AA849565'

DECLARE @nowTimeUTC int = dbo.GetUnixTime(getutcdate())

IF NOT EXISTS(SELECT 1 FROM GXUniqueSQLScriptExecTracking WITH(NOLOCK) WHERE SqlScriptGUID = @updateAlertTokenGUID) 

BEGIN		
	INSERT INTO GXUniqueSQLScriptExecTracking (SqlScriptGUID, Name, Description, CreatedTime, CreatedRelId, ExpireTime, ExpireRelId)


VALUES(@updateAlertTokenGUID, 'UpdateTableDataToTextData', 'One time script replace table data token with text data token for text notificationtypes', @nowTimeUTC, 16, 0 , (16 + 2))

	-- Replace <TABLE DATA> with <TEXT DATA> in text messages for admin alert

	UPDATE mf SET formatstring = REPLACE(formatString, tabledata.Message, textdata.Message)

	FROM NTmessageFormat mf

INNER JOIN NTnotificationRule nt WITH(NOLOCK) on nt.id = mf.NTnotificationID AND nt.alertTypeId = 75

INNER JOIN (SELECT localeid, message FROM evlocalemsgs WITH(NOLOCK) WHERE messageid= (1142 | (CAST(POWER(2, 24) AS BIGINT) * 74)))tabledata ON tableData.LocaleID = nt.locale

INNER JOIN (SELECT localeid, message FROM evlocalemsgs WITH(NOLOCK) WHERE messageid= (1412 | (CAST(POWER(2, 24) AS BIGINT) * 74)))textdata ON textdata.LocaleID =nt.locale

	-- delete  entries where tokenvallist is  greater than 100 kb

	DELETE FROM NTNotificationsForCI  WHERE LEN(CAST(tokenvallist AS NVARCHAR(MAX)))>(100*1024)  

	IF EXISTS(SELECT 1 FROM  GxGlobalParam WITH(NOLOCK) WHERE name = 'bPopulatLMtemplate' AND modified = 0)

		UPDATE GxGlobalParam SET value = 'true' WHERE name = 'bPopulatLMtemplate' AND modified = 0

	ELSE
		INSERT INTO GxGlobalParam(name,  value, created, modified)


		VALUES('bPopulatLMtemplate', 'true', @nowTimeUTC, 0)

END

