title: My VARCHAR(MAX) field is capping itself at 4000; what gives?
body: <p>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 <code>VARCHAR(MAX)</code>, 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 <code>VARCHAR(MAX)</code> it is behaving as if it were <code>VARCHAR(4000)</code> and is truncating the data going into it, although it does <strong>NOT</strong> throw any exceptions, it just silently stops concatenating any more data after 4000 characters. </p>
  
  <p>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. </p>
  
  <p><strong>EDIT</strong></p>
  
  <p>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: </p>
  
  <pre><code>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),
                  '&lt;i&gt;'+CONVERT(VARCHAR,SRC.DATED,101)+
                  ' '+CONVERT(VARCHAR,SRC.DATED,8)+
                  ':&lt;/i&gt; &lt;b&gt;'+SRC.SUBJECT+'&lt;/b&gt;'+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),
              '&lt;i&gt;'+CONVERT(VARCHAR,SRC.DATED,101)+
              ' '+CONVERT(VARCHAR,SRC.DATED,8)+
              ':&lt;/i&gt; &lt;b&gt;'+SRC.SUBJECT+'&lt;/b&gt;'+CHAR(13)+
              SRC.BODY+' (Message ID '+CONVERT(VARCHAR,SRC.ROWID)+')'
              +CHAR(13)+CHAR(13)
          )
      );
  </code></pre>
  
  <p><strong>END EDIT</strong></p>
  
  <p>Below is the code of my sproc...</p>
  
  <pre><code>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 + '&lt;i&gt;'+CONVERT(VARCHAR,SRC.DATED,101)+' '
                              +CONVERT(VARCHAR,SRC.DATED,8)
                              +':&lt;/i&gt; &lt;b&gt;'+SRC.SUBJECT+'&lt;/b&gt;'+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), 
                  '&lt;i&gt;'+CONVERT(VARCHAR,SRC.DATED,101)+' '
                      +CONVERT(VARCHAR,SRC.DATED,8)+':&lt;/i&gt; &lt;b&gt;'
                      +SRC.SUBJECT+'&lt;/b&gt;'+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 &gt; @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 = 
              '&lt;style type="text/css"&gt;
                  * {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;}
              &lt;/style&gt;' 
              + @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 &gt; @PRVID
      END
  
      UPDATE system.EMAILQUEUE SET SENT = getdate()
      FROM system.EMAILQUEUE E, @ROWS R WHERE E.ROWID = R.ROWID
  END
  </code></pre>