October 7, 2010 at 10:20 am
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
October 7, 2010 at 11:01 am
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?
October 7, 2010 at 11:33 am
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.
October 7, 2010 at 11:56 am
The messages are being sent through the 2007 version of Microsoft Exchange Server.
October 7, 2010 at 12:44 pm
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
October 7, 2010 at 12:46 pm
Can you post DDL/sample data of the Event Notice table?
October 7, 2010 at 1:18 pm
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