我的一个数据库中有一个表,它是一个电子邮件队列.某些地址的电子邮件会累积到一封电子邮件中,这是由一个电子邮件完成的.在sproc中,我有一个表变量,我用它来构建电子邮件的累积主体,然后循环发送每封电子邮件.在我的表格中,我将我的正文列定义为VARCHAR(MAX),因为当前可能会为给定的电子邮件地址累积任意数量的电子邮件.虽然我的列被定义为VARCHAR(MAX),但它的行为就好像它是VARCHAR(4000)并且正在截断进入它的数据,尽管它不会抛出任何异常,它只是默默地停止连接4000个字符后的数据.

MERGE语句是将累积的电子邮件正文构建到@ EMAILS.BODY的位置,该字段将自身截断为4000个字符.

编辑

我已经更新了我的MERGE语句,试图将整个分配的字符串转换为VARCHAR(MAX),但它仍然默默地将自己截断为4000个字符…这是我的新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,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,CONVERT(VARCHAR(MAX),101)+
            ' '+CONVERT(VARCHAR,8)+
            ':</i> <b>'+SRC.SUBJECT+'</b>'+CHAR(13)+
            SRC.BODY+' (Message ID '+CONVERT(VARCHAR,SRC.ROWID)+')'
            +CHAR(13)+CHAR(13)
        )
    );

结束编辑

以下是我的代码…

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 + ',DST.BODY = DST.BODY + '<i>'+CONVERT(VARCHAR,101)+' '
                            +CONVERT(VARCHAR,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,BODY) VALUES (
                SRC.ADDRESS,101)+' '
                    +CONVERT(VARCHAR,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,@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

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