My VARCHAR(MAX) field is capping itself at 4000; what gives?
- by eidylon
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.
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
