Field VARCHAR (MAX) closes at 4000; what gives?

Question

I have a table in one of my databases, which is a queue of letters. Emails to specific addresses are accumulated in a single email that sproc runs. In sproc, I have a table variable that I use to create the accumulated bodies of letters, and then scroll through them to send each email. In my var table, I have a body column defined as VARCHAR(MAX) , seeing that there can be any number of emails that are currently accumulating for a given email address. It seems that although my column is defined as VARCHAR(MAX) , it behaves as if it is VARCHAR(4000) , and truncates the data included in it, although it does NOT throw any exceptions, it just silently stops the concatenation of any data after 4000 characters.

The MERGE statement is to create an accumulated email body in @ EMAILS.BODY, which is a field truncated by 4000 characters.

EDIT

I updated my MERGE statement in an attempt to pass the entire assigned VARCHAR (MAX) line, but it still silently truncates itself to 4000 characters ... 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

gbn and Geoffrey, thank you for your help, you made me go in the right direction. Although after some logging and verification, this actually concatenates my string just fine.

The problem is not with the type or length of my column, but with the call of my .NET SendMail procedure, which only accepts NVARCHAR (4000) for the BODY argument ... an obvious translation of the .NET SqlString .

So now I'm on a hunt to figure out how to pass longer strings to the CLR build function.

Answer 2

Corrected...

The table can be executed using varchar (max), but the assigned values ​​are nvarchar (4000)

I.e

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

The right side will remain at maximum nvarchar (4000) due to datatype priority. nvarchar> varchar. When assigned to the max column, it truncates

You will need to provide all the values ​​on the right in varchar

It is still like an integer division ... what confused me is the limit of 4000, when varchar is 8000 ... that means nvarchar is somewhere.

For Nvarchar (Max) Do I only get 4000 characters in TSQL?

Answer 3

http://blogs.infosupport.com/blogs/marks/archive/2011/03/22/take-your-varchar-to-the-max.aspx?CommentPosted=true#commentmessage

This problem and its solution are very well explained in this article, the solution is to add VARCHAR (MAX) to the concatenation

AS IN

DECLARE @SQL VARCHAR (MAX) SET @SQL = '' SET @SQL = @SQL + 'xxxxxx (n)'

Answer 4

I suspect the problem is with string and conversion operations. Try changing your conversions to VARCHAR (max) or convert the entire expression to VARCHAR (max).