My VARCHAR(MAX) field is capping itself at 4000; what gives?

Question

Hello all... I have a table in one of my databases which is a queue of emails. Emails to certain addresses get accumulated into one email, which is done by a sproc. In the sproc, I have a table variable which I use to build the accumulated bodies of the emails, and then loop through to send each email. In my table var I have my body column defined as VARCHAR(MAX), seeing as there could be any number of emails currently accumulated for a given email address. It seems though that even though my column is defined as VARCHAR(MAX) it is behaving as if it were VARCHAR(4000) and is truncating the data going into it, although it does NOT throw any exceptions, it just silently stops concatenating any more data after 4000 characters.

The MERGE statement is where it is building the accumulated email body into @EMAILS.BODY, which is the field that is truncating itself at 4000 characters.

EDIT

I have updated my MERGE statement in an attempt to cast the whole assigned string to VARCHAR(MAX), but it is still silently truncating itself to 4000 chars... here is my new MERGE:

MERGE @EMAILS AS DST 
USING (SELECT * FROM @ROWS WHERE ROWID = @CURRID) AS SRC 
ON SRC.ADDRESS = DST.ADDRESS 
WHEN MATCHED THEN 
    UPDATE SET 
        DST.ALLIDS = DST.ALLIDS + ', ' + CONVERT(VARCHAR,ROWID), 
        DST.BODY = DST.BODY + 
            CONVERT(VARCHAR(MAX),
                '<i>'+CONVERT(VARCHAR,SRC.DATED,101)+
                ' '+CONVERT(VARCHAR,SRC.DATED,8)+
                ':</i> <b>'+SRC.SUBJECT+'</b>'+CHAR(13)+
                SRC.BODY+' (Message ID '+
                CONVERT(VARCHAR,SRC.ROWID)+')'+
                CHAR(13)+CHAR(13)
            )
WHEN NOT MATCHED BY TARGET THEN 
    INSERT (ADDRESS, ALLIDS, BODY) VALUES (
        SRC.ADDRESS, 
        CONVERT(VARCHAR,ROWID), 
        CONVERT(VARCHAR(MAX),
            '<i>'+CONVERT(VARCHAR,SRC.DATED,101)+
            ' '+CONVERT(VARCHAR,SRC.DATED,8)+
            ':</i> <b>'+SRC.SUBJECT+'</b>'+CHAR(13)+
            SRC.BODY+' (Message ID '+CONVERT(VARCHAR,SRC.ROWID)+')'
            +CHAR(13)+CHAR(13)
        )
    );

END EDIT

Below is the code of my sproc...

ALTER PROCEDURE [system].[SendAccumulatedEmails]
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @SENTS  BIGINT = 0;

    DECLARE @ROWS TABLE (
        ROWID    ROWID, 
        DATED    DATETIME, 
        ADDRESS  NAME, 
        SUBJECT  VARCHAR(1000), 
        BODY     VARCHAR(MAX)
    )
    INSERT INTO @ROWS SELECT ROWID, DATED, ADDRESS, SUBJECT, BODY 
    FROM system.EMAILQUEUE 
        WHERE ACCUMULATE = 1 AND SENT IS NULL
        ORDER BY ADDRESS, DATED

    DECLARE @EMAILS TABLE (
        ADDRESS  NAME, 
        ALLIDS   VARCHAR(1000),
        BODY     VARCHAR(MAX) 
    )

    DECLARE @PRVRID ROWID = NULL, @CURRID ROWID = NULL
    SELECT @CURRID = MIN(ROWID) FROM @ROWS
    WHILE @CURRID IS NOT NULL BEGIN
        MERGE @EMAILS AS DST 
        USING (SELECT * FROM @ROWS WHERE ROWID = @CURRID) AS SRC 
        ON SRC.ADDRESS = DST.ADDRESS 
        WHEN MATCHED THEN 
            UPDATE SET 
                DST.ALLIDS = DST.ALLIDS + ', ' + CONVERT(VARCHAR,ROWID), 
                DST.BODY = DST.BODY + '<i>'+CONVERT(VARCHAR,SRC.DATED,101)+' '
                            +CONVERT(VARCHAR,SRC.DATED,8)
                            +':</i> <b>'+SRC.SUBJECT+'</b>'+CHAR(13)+SRC.BODY
                            +' (Message ID '+CONVERT(VARCHAR,SRC.ROWID)+')'
                            +CHAR(13)+CHAR(13)
        WHEN NOT MATCHED BY TARGET THEN 
            INSERT (ADDRESS, ALLIDS, BODY) VALUES (
                SRC.ADDRESS, 
                CONVERT(VARCHAR,ROWID), 
                '<i>'+CONVERT(VARCHAR,SRC.DATED,101)+' '
                    +CONVERT(VARCHAR,SRC.DATED,8)+':</i> <b>'
                    +SRC.SUBJECT+'</b>'+CHAR(13)+SRC.BODY
                    +' (Message ID '+CONVERT(VARCHAR,SRC.ROWID)+')'
                    +CHAR(13)+CHAR(13));

        SELECT @PRVRID = @CURRID, @CURRID = NULL
        SELECT @CURRID = MIN(ROWID) FROM @ROWS WHERE ROWID > @PRVRID
    END 

    DECLARE @MAILFROM VARCHAR(100) = system.getOption('MAILFROM'), 
    DECLARE @SMTPHST VARCHAR(100) = system.getOption('SMTPSERVER'), 
    DECLARE @SMTPUSR VARCHAR(100) = system.getOption('SMTPUSER'), 
    DECLARE @SMTPPWD VARCHAR(100) = system.getOption('SMTPPASS')

    DECLARE @ADDRESS NAME, @BODY VARCHAR(MAX), @ADDL VARCHAR(MAX)
    DECLARE @SUBJECT VARCHAR(1000) = 'Accumulated Emails from LIJSL'

    DECLARE @PRVID NAME = NULL, @CURID NAME = NULL 
    SELECT @CURID = MIN(ADDRESS) FROM @EMAILS
    WHILE @CURID IS NOT NULL BEGIN
        SELECT @ADDRESS = ADDRESS, @BODY = BODY 
        FROM @EMAILS WHERE ADDRESS = @CURID

        SELECT @BODY = @BODY + 'This is an automated message sent from an unmonitored mailbox.'+CHAR(13)+'Do not reply to this message; your message will not be read.'
        SELECT @BODY = 
            '<style type="text/css">
                * {font-family: Tahoma, Arial, Verdana;}
                p {margin-top: 10px; padding-top: 10px; border-top: single 1px dimgray;} 
                p:first-child {margin-top: 10px; padding-top: 0px; border-top: none 0px transparent;}
            </style>' 
            + @BODY 

        exec system.LogIt @SUBJECT, @BODY

        BEGIN TRY 
            exec system.SendMail @SMTPHST, @SMTPUSR, @SMTPPWD, @MAILFROM, 
                             @ADDRESS, NULL, NULL, @SUBJECT, @BODY, 1
        END TRY 
        BEGIN CATCH
            DECLARE @EMSG NVARCHAR(2048) = 'system.EMAILQUEUE.AI:'+ERROR_MESSAGE()
            SELECT @ADDL = 'TO:'+@ADDRESS+CHAR(13)+'SUBJECT:'+@SUBJECT+CHAR(13)+'BODY:'+@BODY
            exec system.LogIt @EMSG,@ADDL
        END CATCH

        SELECT @PRVID = @CURID, @CURID = NULL
        SELECT @CURID = MIN(ADDRESS) FROM @EMAILS WHERE ADDRESS > @PRVID
    END

    UPDATE system.EMAILQUEUE SET SENT = getdate()
    FROM system.EMAILQUEUE E, @ROWS R WHERE E.ROWID = R.ROWID
END

Answer 1

Corrected...

The table may by varchar(max) but the values you assign are only nvarchar(4000)

That is,

maxcolumn = maxvalues + smallstring1 + **unicodestring** + smallstring3 + smallstring4 ...

The right hand side will stay at nvarchar(4000) maximum because of datatype precedence. nvarchar > varchar. When assigned to the max column it truncates

You'll have to ensure all values on the right at varchar

It's still like integer division... what confused me was the 4000 limit when varchar is 8000... this implies nvarchar somewhere.

For Nvarchar(Max) I am only getting 4000 characters in TSQL?

Answer 2

I suspect the problem lies in the string and conversion operations. Try changing your conversions to VARCHAR(max) or converting the entire expression to VARCHAR(max).

Answer 3

gbn and Jeffrey, thank you for you help, you got me going in the right direction. Though after some logging and checking, it actually is concatenating my string just fine.

The problem was not with my column datatype or length, but with the call to my .NET SendMail procedure, which is only accepting NVARCHAR(4000) for the BODY argument... the apparent translation of the .NET SqlString type.

So now I am off on a hunt to figure how to pass longer strings into a CLR assembly function.