Not certain what is causing the problem, nothing in the code should limit the length apart from the maximum 2Gb limit of the varchar(max). Consider the following example

😎

GO

SET NOCOUNT ON;

/* Create a 24000 character string variable

Replicate has a limit of 8000 bytes, so we fill one variable

and then concatenate into another three times the value

*/

DECLARE @TSTR_1 VARCHAR(MAX) = REPLICATE('1234567890',800)

DECLARE @TSTR_2 VARCHAR(MAX) = @TSTR_1 + @TSTR_1 + @TSTR_1;

DECLARE @SAMPLE_DATA TABLE

(

CallStartDateTime DATE NOT NULL

,MenuNames VARCHAR(MAX) NOT NULL

);

INSERT INTO @SAMPLE_DATA(CallStartDateTime,MenuNames)

VALUES

('2014-09-18','srinivas;saipu;vasu;krishna;srinivas;saipu;krishna')

,('2014-09-17','srinivas;saipu;vasu;krishna;srinivas;saipu;krishna;vasu;saipu')

,('2014-09-18','krishna;srinivas;saipu;vasu')

,('2014-09-18','saipu;vasu;a1;a2;a3;.......a100')

,('2014-09-16','saipu;vasu;a1;a2;a3;.......a100')

,('2014-09-18',@TSTR_2 );

;WITH DISTINCT_DATES AS

(

SELECT DISTINCT

SD.CallStartDateTime

FROM @SAMPLE_DATA SD

)

SELECT

DD.CallStartDateTime

,STUFF((SELECT CHAR(44) + SD.MenuNames

FROM @SAMPLE_DATA SD

WHERE DD.CallStartDateTime = SD.CallStartDateTime

FOR XML PATH(''),TYPE).value('.[1]','VARCHAR(MAX)'),1,1,'') AS MenuNames

,LEN(STUFF((SELECT CHAR(44) + SD.MenuNames

FROM @SAMPLE_DATA SD

WHERE DD.CallStartDateTime = SD.CallStartDateTime

FOR XML PATH(''),TYPE).value('.[1]','VARCHAR(MAX)'),1,1,'')) AS Menu_Len

FROM DISTINCT_DATES DD;

Results (chopped)

CallStartDateTime MenuNames Menu_Len

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

2014-09-16 saipu;vasu;a1;a2;a3;..... 31

2014-09-17 srinivas;saipu;vasu;krish 61

2014-09-18 srinivas;saipu;vasu;krish.. 24111