My VARCHAR(MAX) field is capping itself at 4000; what gives?
Solution 1
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.
Solution 2
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?
Solution 3
This problem and solution to it are very well explained in the above article, the solution is to add to the concatenation a VARCHAR(MAX)
AS IN
DECLARE @SQL VARCHAR(MAX) SET @SQL = '' SET @SQL = @SQL + 'xxxxxx(n)'
Solution 4
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).
eidylon
Software developer working primarily in VB.NET, C# and Sql Server. Occasionally working in ORACLE. I love RegEx and jQuery, and have just started playing around with WebAPI. Hobbyist prop maker. Have created several Mass Effect replica weapons as well as a number of custom-designed commission pieces. Projects and pictures on my Facebook page... https://www.facebook.com/darkchannelprops
Updated on June 23, 2022Comments
-
eidylon almost 2 years
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 asVARCHAR(MAX)
it is behaving as if it wereVARCHAR(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
-
eidylon about 14 yearsWell, in the MERGE statement, it is setting DST.BODY = DST.BODY + <smallstrings>. Since DST.BODY is on the right hand side, and is defined VARCHAR(MAX), wouldn't that fulfill this?
-
ZygD about 14 years@eidylon: what about the INSERT...?
-
Jeffrey L Whitledge about 14 years@gbn - SRC.BODY should promote the entire expression to MAX, if I'm reading MSDN correctly.
-
eidylon about 14 yearsMy UPDATE now does DST.BODY = DST.BODY + CONVERT(VARCHAR(MAX),<smallstrings>) and the INSERT likewise inserts CONVERT(VARCHAR(MAX),<smallstrings>) to DST.BODY, but it is still truncating.
-
Jeffrey L Whitledge about 14 years"If the result of the concatenation of strings exceeds the limit of 8,000 bytes, the result is truncated. However, if at least one of the strings concatenated is a large value type, truncation does not occur."
-
eidylon about 14 yearsOkay, I tried wrapping the entire expression being assigned/concatenated to DST.BODY to max as CONVERT(VARCHAR(MAX),<bodyExpression>) and it is still silently truncating anything past 4000.
-
eidylon about 14 years@Jeffrey - hmmmm, then I would think my code should work. The INSERT case assertively never inserts more than about 400 characters, and in the case of the UPDATE, one of the concatenated strings (namely DST.BODY on the RHS) is a large type.
-
ZygD about 14 years@Jeffrey L Whitledge, @eidylon: OK, I was partially correct... updated now
-
Jim L about 14 yearsThe real lesson here is that varchar(max) generally appears to behave like varchar, but sometimes behaves like a totally different data type. Concatenation is one of those times. AFAIK, you always have to specify varchar(max) on converts if that's what you're after.
-
Jeffrey L Whitledge about 14 yearsI looked for a precedence problem with NVARCHAR, but I missed it. I guess I shouldn't rely on my eyes for searching text strings!
-
Jeffrey L Whitledge about 14 years@eidylon - The solution, as always, is to use Unicode for everything! I haven't used an 8-bit codepage in years, and I'll never look back! :-)
-
eidylon about 14 yearsFYI to any interested... passing an NVARCHAR(MAX) field into a CLR sproc is done by decorating the specific parameter with the attribute <SqlFacet(MaxSize:=-1)>
-
ZygD about 14 yearsSo you didn't actually know that that data in the table was OK? You never though to do LEN on it? So the question is wrong and misleading...
-
eidylon about 14 yearsI had figured, obviously incorrectly, that it had to be a problem with the T-SQL code. I hadn't thought of looking at the .NET code because when it comes to .NET I'm used to dealing with just Strings, where there is no concern with length. I have not done a lot of .NET/CLR-SQL integration, and so didn't think to look there. We've all had days where we've missed the obvious for it staring us in the face and needed a fresh set of eyes to help point us in the right direction.
-
Dale K almost 10 yearsWho would have believed! What a strange behavior! Thanks for clearing that up.