sp_send_dbmail Won't Send When Body Longer Than 86 Characters

  • This is running on SQL Server 2008 R2 Enterprise 64 Bit Edition. In the following procedure, I am trying to pull elements of an email message from a table called EventNotice, and use those values when executing sp_send_dbmail:

    DECLARE

    @EventNoticeID Int,

    @Profile_NameLocal varchar(10),

    @SubjectTextLocal varchar(200),

    @BodyLocal nvarchar(MAX),

    @Body_FormatLocal varchar(50),

    @ToLocal nvarchar(500),

    @PackageNameLocal varchar(100),

    @MachineNameLocal varchar(100)

    SET @PackageNameLocal = 'CreatePositivePay'

    SET @MachineNameLocal = 'NewMachine'

    SET @EventNoticeID = 3

    SELECT

    @Profile_NameLocal = Profile_Name,

    @SubjectTextLocal = @PackageNameLocal + ' executed on ' + @MachineNameLocal + '. ' + SubjectText,

    @BodyLocal = BodyText,

    @ToLocal = ToRecipient,

    @Body_FormatLocal = Body_Format

    FROM dbo.EventNotice

    WHERE EventNoticeID = @EventNoticeID

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = @Profile_NameLocal,

    @subject = @SubjectTextLocal,

    @body = @BodyLocal,

    @recipients = @ToLocal,

    @body_format = @Body_FormatLocal

    When I execute sp_send_dbmail with hard coded values, the message delivery is successful. When try do set the parameters with variable values, 'Mail Queued' is return in the Management Studio, but the message does not appear in the inbox of my email client.

    Here are the variable values that sp_send_dbmail is trying to consume:

    @PackageNameLocal = 'CreatePositivePay',

    @MachineNameLocal = 'LocalMachine',

    @ProcessDateLocal = '2010-10-07 11:17:50.853',

    @Profile_NameLocal = 'Reporting',

    @SubjectTextLocal = 'No Records Found in ViewAPPositivePayEntries',

    @BodyLocal = 'No Records where available to be transmitted to The Private Bank. CreatePositivePay ran on LocalMachine.',

    @ToLocal = 'dsmith@email.com; cdunn@email.com',

    @Body_FormatLocal = 'html'

    The problem appears to be with @BodyLocal. For some reason, if I shorten the string to 86 characters, sp_send_dbmail can send the message. Any longer than that, and the message does not get delivered. The type for @BodyLocal is nvarchar(max).

    When the message does not go out, and entry is not written into the Database Mail Log.

    Here is the structure of the EventNotice table:

    CREATE TABLE [dbo].[EventNotice](

    [EventNoticeID] [int] IDENTITY(1,1) NOT NULL Primary Key Clustered,

    [EventNoticeName] [varchar](100) NOT NULL,

    [Profile_Name] [varchar](10) NOT NULL,

    [SubjectText] [nvarchar](255) NOT NULL,

    [BodyText] [nvarchar](max) NOT NULL,

    [Body_Format] [varchar](20) NOT NULL,

    [ToRecipient] [varchar](max) NOT NULL,

    [CcRecipient] [varchar](max) NULL,

    [BccRecipient] [varchar](max) NULL,

    [Handler] [nvarchar](128) NULL,

    What am I doing wrong?

    Thank you for your help!

    CSDunn

  • I doubled check and BOL does say " [ @body= ] 'body' Is the body of the e-mail message. The message body is of type nvarchar(max), with a default of NULL."

    maybe try setting the col to nvarcahr255 and see what happens? I know it should work the way you have it though?

  • The same thing happens for the Subject. If the string is beyond a certain small number of characters in length, the email is not sent.

  • The messages are being sent through the 2007 version of Microsoft Exchange Server.

  • Below is the actual stored procedure that I'm using:

    CREATE PROCEDURE [dbo].[spETL_CreatePositivePay_EventNoticeNoPayEntries]

    (

    @PackageName nvarchar(100),

    @MachineName nvarchar(100)

    )

    AS

    DECLARE

    @EventNoticeIDLocal Int,

    @Profile_NameLocal varchar(10),

    @SubjectTextLocal nvarchar(255),

    @BodyLocal nvarchar(max),

    @Body_FormatLocal varchar(20),

    @ToLocal varchar(max),

    @PackageNameLocal nvarchar(100),

    @MachineNameLocal nvarchar(100),

    @ProcessDateLocal datetime

    SELECT@PackageNameLocal = @PackageName,

    @MachineNameLocal = @MachineName,

    @ProcessDateLocal = GETDATE()

    BEGIN TRY

    SET @EventNoticeIDLocal = 3

    SELECT

    @Profile_NameLocal= Profile_Name,

    @SubjectTextLocal= SubjectText,

    @BodyLocal= BodyText,

    @ToLocal= ToRecipient,

    @Body_FormatLocal= Body_Format

    FROM dbo.EventNotice

    WHERE EventNoticeID = @EventNoticeIDLocal

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name= @Profile_NameLocal,

    @subject = @SubjectTextLocal,

    @body= @BodyLocal,

    @recipients= @ToLocal,

    @body_format= @Body_FormatLocal

    END TRY

    BEGIN CATCH

    EXEC dbo.spLogError

    @PackageName = @PackageNameLocal,

    @MachineName = @MachineNameLocal,

    @ProcessName = 'spETL_EventNoticeNoPayEntries_CreatePositivePay',

    @ProcessDate = @ProcessDateLocal

    END CATCH

  • Can you post DDL/sample data of the Event Notice table?

  • This turned out to be a problem with a rule on the Exchange server. The proc works as intended.

    Thank you for your help!

    CSDunn

Viewing 7 posts - 1 through 6 (of 6 total)

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