sp_send_dbmail and varchar(max)

• SQLArch

  Ten Centuries

  Points: 1322

  More actions

  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

   

• Aaron Ingold

  SSCrazy Eights

  Points: 9070

  More actions

  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.

• SQLArch

  Ten Centuries

  Points: 1322

  More actions

  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

• Aaron Ingold

  SSCrazy Eights

  Points: 9070

  More actions

  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.

• SQLArch

  Ten Centuries

  Points: 1322

  More actions

  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!