drunk_irishman
asked on
Email body cuts off
I have a table (To, Subject, and Body fields) in my SQL database that has a trigger set up so that when I add a new record it kicks off an email. My program has many different emails going out so I just use the one central table to send them out and that way I have a record of every email that goes out. Oh and I have it set to send in HTML format.
Been using it fine like this for years, here's the issue. A new email I need to send out contains a pretty good amount of data so I am using <table>, <tr>, <td> to format it make it look good. All the data is getting into the table fine, but when I receieve the actual email....the body of the email cuts off about half way through. Any ideas?
Been using it fine like this for years, here's the issue. A new email I need to send out contains a pretty good amount of data so I am using <table>, <tr>, <td> to format it make it look good. All the data is getting into the table fine, but when I receieve the actual email....the body of the email cuts off about half way through. Any ideas?
ASKER
it's varchar(8000) but the data I'm putting doesn't come close to 8000 characters.
ASKER
There are 1200 characters in the Body. It appears to be cutting off roughly around the 500 character mark.
ASKER
Also, the trigger is set up to kick off msdb.dbo.sp_send_dbmail and in there I have the Body variable initialized as NVARCHAR(MAX).
Are you counting the <table> tags in the number of characters too?
ASKER
Yes.
Can you post a sample (your 1200 characters) which got cut off here so that we can verify..
Kindly post the msdb.dbo.sp_send_dbmail script which got executed to verify for any issues in it.
Kindly post the msdb.dbo.sp_send_dbmail script which got executed to verify for any issues in it.
ASKER
Here is the body of the email.
<table><tr><td>Job #:</td><td>64903</td></tr><tr><td>Customer:</td><td>City of Elkhart</td></tr><tr><td>Description:</td><td>Water Quality Report Newsletter</td></tr><tr><td>Quantity:</td><td>18,000</td></tr><tr><td>Ship Date:</td><td>6/11/2010</td></tr><tr><td><br></td></tr></table><table><tr><td>SubJob:</td><td>1</td></tr><tr><td>Description:</td><td>Water Quality Report Newsletter</td></tr><tr><td>Pages:</td><td>2</td></tr><tr><td>Forms:</td><td>1</td></tr><tr><td>Flat Size:</td><td>17.0000 X 11.0000</td></tr><tr><td>Folded Size:</td><td>8.5000 X 5.5000</td></tr><tr><td>Colors:</td><td>4</td></tr><tr><td>Paper:</td><td>80# 24x36 #3 Gloss Text 146M</td></tr><tr><td>Bindery Operations:</td><td>Cut Fold </td></tr><tr><td><br></td></tr><tr><td>Overall Bindery:</td><td>Mail </td></tr></table><table><tr><td>Prepress:</td><td>Forms 1-1: PDF proof to someone@coei.org </td></tr><tr><td>Press:</td><td></td></tr><tr><td>Bindery:</td><td>Forms 1-1: Trim, Fold, wafer seal and ink jet address 17,143 approx. Box balance </td></tr><tr><td>Shipping:</td><td>Forms 1-1: Balance to: \r\nSome Person\r\nCity of Elkhart\r\n1717 West Lusher Ave. \r\nElkhart IN 46516 </td></tr></table>
ASKER
And here is msdb.dbo.sp_send_dbmail
USE [msdb]
GO
/****** Object: StoredProcedure [dbo].[sp_send_dbmail] Script Date: 06/10/2010 09:43:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
-- sp_sendemail : Sends a mail from Yukon outbox.
--
ALTER PROCEDURE [dbo].[sp_send_dbmail]
@profile_name sysname = NULL,
@recipients VARCHAR(MAX) = NULL,
@copy_recipients VARCHAR(MAX) = NULL,
@blind_copy_recipients VARCHAR(MAX) = NULL,
@subject NVARCHAR(255) = NULL,
@body NVARCHAR(MAX) = NULL,
@body_format VARCHAR(20) = 'HTML',
@importance VARCHAR(6) = 'NORMAL',
@sensitivity VARCHAR(12) = 'NORMAL',
@file_attachments NVARCHAR(MAX) = NULL,
@query NVARCHAR(MAX) = NULL,
@execute_query_database sysname = NULL,
@attach_query_result_as_file BIT = 0,
@query_attachment_filename NVARCHAR(260) = NULL,
@query_result_header BIT = 1,
@query_result_width INT = 256,
@query_result_separator CHAR(1) = ' ',
@exclude_query_output BIT = 0,
@append_query_error BIT = 0,
@query_no_truncate BIT = 0,
@query_result_no_padding BIT = 0,
@mailitem_id INT = NULL OUTPUT
WITH EXECUTE AS 'dbo'
AS
BEGIN
SET NOCOUNT ON
-- And make sure ARITHABORT is on. This is the default for yukon DB's
SET ARITHABORT ON
--Declare variables used by the procedure internally
DECLARE @profile_id INT,
@temp_table_uid uniqueidentifier,
@sendmailxml VARCHAR(max),
@CR_str NVARCHAR(2),
@localmessage NVARCHAR(255),
@QueryResultsExist INT,
@AttachmentsExist INT,
@RetErrorMsg NVARCHAR(4000), --Impose a limit on the error message length to avoid memory abuse
@rc INT,
@procName sysname,
@trancountSave INT,
@tranStartedBool INT,
@is_sysadmin BIT,
@send_request_user sysname,
@database_user_id INT
-- Initialize
SELECT @rc = 0,
@QueryResultsExist = 0,
@AttachmentsExist = 0,
@temp_table_uid = NEWID(),
@procName = OBJECT_NAME(@@PROCID),
@tranStartedBool = 0,
@trancountSave = @@TRANCOUNT
EXECUTE AS CALLER
SELECT @is_sysadmin = IS_SRVROLEMEMBER('sysadmin'),
@send_request_user = SUSER_SNAME(),
@database_user_id = USER_ID()
REVERT
--Check if SSB is enabled in this database
IF (ISNULL(DATABASEPROPERTYEX(DB_NAME(), N'IsBrokerEnabled'), 0) <> 1)
BEGIN
RAISERROR(14650, 16, 1)
RETURN 1
END
--Report error if the mail queue has been stopped.
--sysmail_stop_sp/sysmail_start_sp changes the receive status of the SSB queue
IF NOT EXISTS (SELECT * FROM sys.service_queues WHERE name = N'ExternalMailQueue' AND is_receive_enabled = 1)
BEGIN
RAISERROR(14641, 16, 1)
RETURN 1
END
-- Get the relevant profile_id
--
IF (@profile_name IS NULL)
BEGIN
-- Use the global or users default if profile name is not supplied
SELECT TOP (1) @profile_id = pp.profile_id
FROM msdb.dbo.sysmail_principalprofile as pp
WHERE (pp.is_default = 1) AND
(dbo.get_principal_id(pp.principal_sid) = @database_user_id OR pp.principal_sid = 0x00)
ORDER BY dbo.get_principal_id(pp.principal_sid) DESC
--Was a profile found
IF(@profile_id IS NULL)
BEGIN
RAISERROR(14636, 16, 1)
RETURN 1
END
END
ELSE
BEGIN
--Get primary account if profile name is supplied
EXEC @rc = msdb.dbo.sysmail_verify_profile_sp @profile_id = NULL,
@profile_name = @profile_name,
@allow_both_nulls = 0,
@allow_id_name_mismatch = 0,
@profileid = @profile_id OUTPUT
IF (@rc <> 0)
RETURN @rc
--Make sure this user has access to the specified profile.
--sysadmins can send on any profiles
IF ( @is_sysadmin <> 1)
BEGIN
--Not a sysadmin so check users access to profile
iF NOT EXISTS(SELECT *
FROM msdb.dbo.sysmail_principalprofile
WHERE ((profile_id = @profile_id) AND
(dbo.get_principal_id(principal_sid) = @database_user_id OR principal_sid = 0x00)))
BEGIN
RAISERROR(14607, -1, -1, 'profile')
RETURN 1
END
END
END
--Attach results must be specified
IF @attach_query_result_as_file IS NULL
BEGIN
RAISERROR(14618, 16, 1, 'attach_query_result_as_file')
RETURN 2
END
--No output must be specified
IF @exclude_query_output IS NULL
BEGIN
RAISERROR(14618, 16, 1, 'exclude_query_output')
RETURN 3
END
--No header must be specified
IF @query_result_header IS NULL
BEGIN
RAISERROR(14618, 16, 1, 'query_result_header')
RETURN 4
END
-- Check if query_result_separator is specifed
IF @query_result_separator IS NULL OR DATALENGTH(@query_result_separator) = 0
BEGIN
RAISERROR(14618, 16, 1, 'query_result_separator')
RETURN 5
END
--Echo error must be specified
IF @append_query_error IS NULL
BEGIN
RAISERROR(14618, 16, 1, 'append_query_error')
RETURN 6
END
--@body_format can be TEXT (default) or HTML
IF (@body_format IS NULL)
BEGIN
SET @body_format = 'TEXT'
END
ELSE
BEGIN
SET @body_format = UPPER(@body_format)
IF @body_format NOT IN ('TEXT', 'HTML')
BEGIN
RAISERROR(14626, 16, 1, @body_format)
RETURN 13
END
END
--Importance must be specified
IF @importance IS NULL
BEGIN
RAISERROR(14618, 16, 1, 'importance')
RETURN 15
END
SET @importance = UPPER(@importance)
--Importance must be one of the predefined values
IF @importance NOT IN ('LOW', 'NORMAL', 'HIGH')
BEGIN
RAISERROR(14622, 16, 1, @importance)
RETURN 16
END
--Sensitivity must be specified
IF @sensitivity IS NULL
BEGIN
RAISERROR(14618, 16, 1, 'sensitivity')
RETURN 17
END
SET @sensitivity = UPPER(@sensitivity)
--Sensitivity must be one of predefined values
IF @sensitivity NOT IN ('NORMAL', 'PERSONAL', 'PRIVATE', 'CONFIDENTIAL')
BEGIN
RAISERROR(14623, 16, 1, @sensitivity)
RETURN 18
END
--Message body cannot be null. Atleast one of message, subject, query,
--attachments must be specified.
IF( (@body IS NULL AND @query IS NULL AND @file_attachments IS NULL AND @subject IS NULL)
OR
( (LEN(@body) IS NULL OR LEN(@body) <= 0)
AND (LEN(@query) IS NULL OR LEN(@query) <= 0)
AND (LEN(@file_attachments) IS NULL OR LEN(@file_attachments) <= 0)
AND (LEN(@subject) IS NULL OR LEN(@subject) <= 0)
)
)
BEGIN
RAISERROR(14624, 16, 1, '@body, @query, @file_attachments, @subject')
RETURN 19
END
ELSE
IF @subject IS NULL OR LEN(@subject) <= 0
SET @subject='SQL Server Message'
--Recipients cannot be empty. Atleast one of the To, Cc, Bcc must be specified
IF ( (@recipients IS NULL AND @copy_recipients IS NULL AND
@blind_copy_recipients IS NULL
)
OR
( (LEN(@recipients) IS NULL OR LEN(@recipients) <= 0)
AND (LEN(@copy_recipients) IS NULL OR LEN(@copy_recipients) <= 0)
AND (LEN(@blind_copy_recipients) IS NULL OR LEN(@blind_copy_recipients) <= 0)
)
)
BEGIN
RAISERROR(14624, 16, 1, '@recipients, @copy_recipients, @blind_copy_recipients')
RETURN 20
END
--If query is not specified, attach results and no header cannot be true.
IF ( (@query IS NULL OR LEN(@query) <= 0) AND @attach_query_result_as_file = 1)
BEGIN
RAISERROR(14625, 16, 1)
RETURN 21
END
--
-- Execute Query if query is specified
IF ((@query IS NOT NULL) AND (LEN(@query) > 0))
BEGIN
EXECUTE AS CALLER
EXEC @rc = sp_RunMailQuery
@query = @query,
@attach_results = @attach_query_result_as_file,
@query_attachment_filename = @query_attachment_filename,
@no_output = @exclude_query_output,
@query_result_header = @query_result_header,
@separator = @query_result_separator,
@echo_error = @append_query_error,
@dbuse = @execute_query_database,
@width = @query_result_width,
@temp_table_uid = @temp_table_uid,
@query_no_truncate = @query_no_truncate,
@query_result_no_padding = @query_result_no_padding
-- This error indicates that query results size was over the configured MaxFileSize.
-- Note, an error has already beed raised in this case
IF(@rc = 101)
GOTO ErrorHandler;
REVERT
-- Always check the transfer tables for data. They may also contain error messages
-- Only one of the tables receives data in the call to sp_RunMailQuery
IF(@attach_query_result_as_file = 1)
BEGIN
IF EXISTS(SELECT * FROM sysmail_attachments_transfer WHERE uid = @temp_table_uid)
SET @AttachmentsExist = 1
END
ELSE
BEGIN
IF EXISTS(SELECT * FROM sysmail_query_transfer WHERE uid = @temp_table_uid AND uid IS NOT NULL)
SET @QueryResultsExist = 1
END
-- Exit if there was an error and caller doesn't want the error appended to the mail
IF (@rc <> 0 AND @append_query_error = 0)
BEGIN
--Error msg with be in either the attachment table or the query table
--depending on the setting of @attach_query_result_as_file
IF(@attach_query_result_as_file = 1)
BEGIN
--Copy query results from the attachments table to mail body
SELECT @RetErrorMsg = CONVERT(NVARCHAR(4000), attachment)
FROM sysmail_attachments_transfer
WHERE uid = @temp_table_uid
END
ELSE
BEGIN
--Copy query results from the query table to mail body
SELECT @RetErrorMsg = text_data
FROM sysmail_query_transfer
WHERE uid = @temp_table_uid
END
GOTO ErrorHandler;
END
SET @AttachmentsExist = @attach_query_result_as_file
END
ELSE
BEGIN
--If query is not specified, attach results cannot be true.
IF (@attach_query_result_as_file = 1)
BEGIN
RAISERROR(14625, 16, 1)
RETURN 21
END
END
--Get the prohibited extensions for attachments from sysmailconfig.
IF ((@file_attachments IS NOT NULL) AND (LEN(@file_attachments) > 0))
BEGIN
EXECUTE AS CALLER
EXEC @rc = sp_GetAttachmentData
@attachments = @file_attachments,
@temp_table_uid = @temp_table_uid,
@exclude_query_output = @exclude_query_output
REVERT
IF (@rc <> 0)
GOTO ErrorHandler;
IF EXISTS(SELECT * FROM sysmail_attachments_transfer WHERE uid = @temp_table_uid)
SET @AttachmentsExist = 1
END
-- Start a transaction if not already in one.
-- Note: For rest of proc use GOTO ErrorHandler for falures
if (@trancountSave = 0)
BEGIN TRAN @procName
SET @tranStartedBool = 1
-- Store complete mail message for history/status purposes
INSERT sysmail_mailitems
(
profile_id,
recipients,
copy_recipients,
blind_copy_recipients,
subject,
body,
body_format,
importance,
sensitivity,
file_attachments,
attachment_encoding,
query,
execute_query_database,
attach_query_result_as_file,
query_result_header,
query_result_width,
query_result_separator,
exclude_query_output,
append_query_error,
send_request_user
)
VALUES
(
@profile_id,
@recipients,
@copy_recipients,
@blind_copy_recipients,
@subject,
@body,
@body_format,
@importance,
@sensitivity,
@file_attachments,
'MIME',
@query,
@execute_query_database,
@attach_query_result_as_file,
@query_result_header,
@query_result_width,
@query_result_separator,
@exclude_query_output,
@append_query_error,
@send_request_user
)
SELECT @rc = @@ERROR,
@mailitem_id = @@IDENTITY
IF(@rc <> 0)
GOTO ErrorHandler;
--Copy query into the message body
IF(@QueryResultsExist = 1)
BEGIN
-- if the body is null initialize it
UPDATE sysmail_mailitems
SET body = N''
WHERE mailitem_id = @mailitem_id
AND body is null
--Add CR
SET @CR_str = CHAR(13) + CHAR(10)
UPDATE sysmail_mailitems
SET body.WRITE(@CR_str, NULL, NULL)
WHERE mailitem_id = @mailitem_id
--Copy query results to mail body
UPDATE sysmail_mailitems
SET body.WRITE( (SELECT text_data from sysmail_query_transfer WHERE uid = @temp_table_uid), NULL, NULL )
WHERE mailitem_id = @mailitem_id
END
--Copy into the attachments table
IF(@AttachmentsExist = 1)
BEGIN
--Copy temp attachments to sysmail_attachments
INSERT INTO sysmail_attachments(mailitem_id, filename, filesize, attachment)
SELECT @mailitem_id, filename, filesize, attachment
FROM sysmail_attachments_transfer
WHERE uid = @temp_table_uid
END
-- Create the primary SSB xml maessage
SET @sendmailxml = '<requests:SendMail xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://schemas.microsoft.com/databasemail/requests RequestTypes.xsd" xmlns:requests="http://schemas.microsoft.com/databasemail/requests"><MailItemId>'
+ CONVERT(NVARCHAR(20), @mailitem_id) + N'</MailItemId></requests:SendMail>'
-- Send the send request on queue.
EXEC @rc = sp_SendMailQueues @sendmailxml
IF @rc <> 0
BEGIN
RAISERROR(14627, 16, 1, @rc, 'send mail')
GOTO ErrorHandler;
END
-- Print success message if required
IF (@exclude_query_output = 0)
BEGIN
SET @localmessage = FORMATMESSAGE(14635)
PRINT @localmessage
END
--
-- See if the transaction needs to be commited
--
IF (@trancountSave = 0 and @tranStartedBool = 1)
COMMIT TRAN @procName
-- All done OK
goto ExitProc;
-----------------
-- Error Handler
-----------------
ErrorHandler:
IF (@tranStartedBool = 1)
ROLLBACK TRAN @procName
------------------
-- Exit Procedure
------------------
ExitProc:
--Always delete query and attactment transfer records.
--Note: Query results can also be returned in the sysmail_attachments_transfer table
DELETE sysmail_attachments_transfer WHERE uid = @temp_table_uid
DELETE sysmail_query_transfer WHERE uid = @temp_table_uid
--Raise an error it the query execution fails
-- This will only be the case when @append_query_error is set to 0 (false)
IF( (@RetErrorMsg IS NOT NULL) AND (@exclude_query_output=0) )
BEGIN
RAISERROR(14661, -1, -1, @RetErrorMsg)
END
RETURN (@rc)
END
>> And here is msdb.dbo.sp_send_dbmail
I meant the script which calls this msdb.dbo.sp_send_dbmail procedure and not the code of this procedure ( as it is available in all server and have it with me)..
I meant the script which calls this msdb.dbo.sp_send_dbmail procedure and not the code of this procedure ( as it is available in all server and have it with me)..
ASKER
Yes but you can change it, so that is why you may have been asking for it. I'm well aware it is available on all servers. Here is the Trigger I have on my tblEmails table, to be more specific.
ALTER TRIGGER [dbo].[SendEmailTrigger] ON [dbo].[tblEMails] AFTER INSERT
AS
DECLARE @to varchar(255)
DECLARE @subj varchar(50)
DECLARE @msg varchar(8000)
SET @to = (SELECT MLTo FROM inserted)
SET @subj = (SELECT MLSubject FROM inserted)
SET @msg = (SELECT MLBody FROM inserted)
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
EXEC msdb.dbo.sp_send_dbmail @recipients=@to, @body= @msg, @subject = @subj, @profile_name = 'DBJobInsertMail'
END
I tried sending your msg from one of the servers I have and cold send the whole thing.
This is what came in my email:
Job #: 64903
Customer: City of Elkhart
Description: Water Quality Report Newsletter
Quantity: 18,000
Ship Date: 6/11/2010
SubJob: 1
Description: Water Quality Report Newsletter
Pages: 2
Forms: 1
Flat Size: 17.0000 X 11.0000
Folded Size: 8.5000 X 5.5000
Colors: 4
Paper: 80# 24x36 #3 Gloss Text 146M
Bindery Operations: Cut Fold
Overall Bindery: Mail
Prepress: Forms 1-1: PDF proof to someone@coei.org
Press:
Bindery: Forms 1-1: Trim, Fold, wafer seal and ink jet address 17,143 approx. Box balance
Shipping: Forms 1-1: Balance to: \r\nSome Person\r\nCity of Elkhart\r\n1717 West Lusher Ave. \r\nElkhart IN 46516
Let me know if it is cutting anything off.
This is what came in my email:
Job #: 64903
Customer: City of Elkhart
Description: Water Quality Report Newsletter
Quantity: 18,000
Ship Date: 6/11/2010
SubJob: 1
Description: Water Quality Report Newsletter
Pages: 2
Forms: 1
Flat Size: 17.0000 X 11.0000
Folded Size: 8.5000 X 5.5000
Colors: 4
Paper: 80# 24x36 #3 Gloss Text 146M
Bindery Operations: Cut Fold
Overall Bindery: Mail
Prepress: Forms 1-1: PDF proof to someone@coei.org
Press:
Bindery: Forms 1-1: Trim, Fold, wafer seal and ink jet address 17,143 approx. Box balance
Shipping: Forms 1-1: Balance to: \r\nSome Person\r\nCity of Elkhart\r\n1717 West Lusher Ave. \r\nElkhart IN 46516
Let me know if it is cutting anything off.
Can you run the code below and ensure that the whole string is coming:
DECLARE @msg varchar(8000)
SET @msg = (SELECT MLBody FROM inserted)
PRINT @msg
DECLARE @msg varchar(8000)
SET @msg = (SELECT MLBody FROM inserted)
PRINT @msg
ASKER
Yes it is.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This sounds like a data length limit. If the data exceeds the storage limit, the excess gets cut off.