Post reply

sp_send_dbmail and varchar(max)

  • May 5, 2006 at 12:10 pm

    #172424

    I am trying to send email reading data of the email transmission table. I am using varchar (max) as I need to send HTML and text contents are more than 8000 characters. It still cuts off at 8000 characters in body text of email.

    Did anybody tried this out or know how to do this?

     

    Thanks

     

  • May 5, 2006 at 1:46 pm

    #636354

    I'm assuming you're saying that you're using @body as the nvarchar(max) value. It would probably help to give an example of the data/code you're working with.

  • May 5, 2006 at 2:27 pm

    #636363

    Sorry about that. See that I declared the variable as varchar(max) but still it truncates at 8000 characters.

    Here is the code:

    Create procedure [dbo].[usp_EmailSendingEngine]

    @DateUpto datetime = null

    AS

    Declare

    @emailTransmissionID int

    Declare

    @emailToAddress varchar(250)

    Declare

    @emailCCAddress varchar(2000)

    Declare

    @emailBCCAddress varchar(2000)

    Declare

    @emailSubject varchar(500)

    Declare

    @emailContents nvarchar(max)

    Declare

    @schduledSendDT datetime

    Declare

    @actualSentDT datetime

    Declare

    @sendFrom varchar(250)

    Declare

    @generatedFrom varchar(100)

    Declare

    @statusID int

    If

    @DateUpto is null or @DateUpto = ''

    Set @DateUpto = getdate()

    DECLARE

    emailBatch CURSOR FOR

    SELECT

    emailTransmissionID, emailToAddress, emailCCAddress, emailBCCAddress, emailSubject, cast(emailContents as varchar(max)), schduledSendDT, actualSentDT, sendFrom, generatedFrom, statusID

    FROM

    EmailTransmission et

    where

    et.deleteDT is null

    and (schduledSendDT is null Or schduledSendDT <= @DateUpto)

    OPEN

    emailBatch

    FETCH

    NEXT FROM emailBatch

    INTO

    @emailTransmissionID, @emailToAddress, @emailCCAddress, @emailBCCAddress, @emailSubject, @emailContents, @schduledSendDT, @actualSentDT, @sendFrom, @generatedFrom, @statusID

    WHILE

    @@FETCH_STATUS = 0

    BEGIN

    EXEC msdb.dbo.sp_send_dbmail @recipients = @emailToAddress, @subject = @emailSubject,

        @body = @emailContents, @body_format = 'HTML' ;

    --if email was successful log it; or mark for Resend or error

    FETCH NEXT FROM emailBatch

    INTO @emailTransmissionID, @emailToAddress, @emailCCAddress, @emailBCCAddress, @emailSubject, @emailContents, @schduledSendDT, @actualSentDT, @sendFrom, @generatedFrom, @statusID

    END

    CLOSE

    emailBatch

    DEALLOCATE

    emailBatch

  • May 5, 2006 at 3:56 pm

    #636386

    In the table EmailTransmission, is the emailContents column also nvarchar(max)? I'd test the length of that column. I'd also make everything consistently nvarchar(max) instead of alternating between varchar(max) and nvarchar(max), though I don't think that would be the problem.

  • May 6, 2006 at 10:01 pm

    #636421

    Actually it is ntext in table. I removed casting to varchar(max) and it worked. Though I don't see any reason why cast is cutting off at 8000 characters.

    Thanks for your help. Appreciated!

     

     

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply