我的VARCHAR(MAX)字段的上限为4000；是什么赋予了? [英] My VARCHAR(MAX) field is capping itself at 4000; what gives?
问题描述
我的一个数据库中有一个表，该表是一列电子邮件.发送到某些地址的电子邮件会累积到一封电子邮件中，这是由存储过程完成的.在该存储过程中，我有一个表变量，该变量用于构建电子邮件的累积正文，然后循环发送每封电子邮件.在我的表var中，我的主体列定义为VARCHAR(MAX)，因为对于给定的电子邮件地址，当前可能积累了任意数量的电子邮件.似乎即使我的列定义为VARCHAR(MAX)，它的行为也好像是VARCHAR(4000)一样，并且正在截断其中的数据，尽管它不不会抛出任何异常，但它只是在4000个字符之后，静默停止连接更多数据.
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.
在MERGE语句中，它将累积的电子邮件正文构建到@ EMAILS.BODY中，该字段本身会被截断为4000个字符.
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.
编辑
我已经更新了MERGE语句，试图将整个分配的字符串转换为VARCHAR(MAX)，但是它仍然默默地将其自身截断为4000个字符...这是我的新MERGE:
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
推荐答案
gbn和Jeffrey，谢谢您的帮助，您使我朝着正确的方向前进.尽管经过一些记录和检查，它实际上是将我的字符串串联在一起就好了.
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.
问题不是我的列数据类型或长度，而是我的.NET SendMail过程的调用，该过程仅接受BODY参数的NVARCHAR(4000)... .NET的明显翻译SqlString类型.
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.
所以现在我开始研究如何将更长的字符串传递给CLR汇编函数.
So now I am off on a hunt to figure how to pass longer strings into a CLR assembly function.
这篇关于我的VARCHAR(MAX)字段的上限为4000；是什么赋予了?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
