more than 4000 characters truncated in mail

Hello,

I am using SQL Server 2000 and Persits mail component to send mail from SQL server.

I have a letter of nearly 6000 characters. When I send that letter from SQL server procedure it truncates body after 4000 characters. But when I send the same letter from aspx page its coming full.

Can you please guide me where I am wrong or what should I do to send it in full?

Thanks,

Haresh

4000 is the limit for NTEXT data type (unicode). So I would think the parameter for the procedure is perhaps declared as NTEXT(4000)?

Actually my column which contains letter body is ntext 16.

Then I do some replace functions as per users (So obviously I convert it in varchar(8000)). Eventhough I convert it in varchar 8000 it's truncating after 4000.

What command do you use for "sending"?

I am using aspEmail component (Persits) to send mail.

Below is my code to send mail:

declare @mail int , @MailID int
EXEC @mail = sp_OACreate 'Persits.MailSender', @MailID OUT
EXEC @mail = sp_OASetProperty @MailID, 'Host', 'mx10.domain.com'
EXEC @mail = sp_OASetProperty @MailID, 'helo', 'domain.com'
EXEC @mail = sp_OASetProperty @MailID, 'From', @FromEmail
EXEC @mail = sp_OASetProperty @MailID, 'MailFrom', @MailFromUID
EXEC @mail = sp_OASetProperty @MailID, 'FromName', @FromName
EXEC @mail = sp_OASetProperty @MailID, 'Body', @Body
EXEC @mail = sp_OASetProperty @MailID, 'Subject', @Subject
EXEC @mail = sp_OAMethod @MailID, 'AddAddress', NULL , @ToEmail , @ToName
EXEC @mail = sp_OASetProperty @MailID, 'IsHTML', 1
EXEC @mail = sp_OASetProperty @MailID, 'Username', @MailFromUID
EXEC @mail = sp_OASetProperty @MailID, 'Password', @MailFromPWD
EXEC @mail = sp_OAMethod @MailID, 'Send'
EXEC @mail = sp_OADestroy @MailID

I would guess the aspEmail component is the most likely cause of the truncation.

I don't think so because when I sent the same letter from aspx page using aspEmail component it's coming with full content.

What data type does the @Body variable have?

@Body varchar(8000)

Please answer whether it's possible or not, because my users are frustrating for their truncating mails.

Thanks,

Haresh

Should I consider that I will not get any help from here or anyone can?

Thanks,

Haresh

What is the syntax for setting the @Body variable to the 8000 character text? Show us some context, and types for all variables involved.

I am extracting letter body from ntext column using below query:


declare @Body varchar(8000)
select @Body = cast(ceEmailContent as varchar(8000)) from tblCampaignEmails where ceCampaignEmailID = 72

then I send my other values and above body value to below procedure:

I created a procedure to send mail which is as below:

CREATE PROCEDURE prc_SendMailPersits
@FromEmail varchar(60),
@FromName varchar(101),
@ToEmail varchar(60),
@ToName varchar(101),
@Subject varchar(200),
@Body varchar(8000),
@MailFromUID varchar(60),
@MailFromPWD varchar(20)
AS
SET NOCOUNT ON
declare @mail int , @MailID int
EXEC @mail = sp_OACreate 'Persits.MailSender', @MailID OUT
EXEC @mail = sp_OASetProperty @MailID, 'Host', 'mx10.domain.com'
EXEC @mail = sp_OASetProperty @MailID, 'helo', 'domain.com'
EXEC @mail = sp_OASetProperty @MailID, 'From', @FromEmail
EXEC @mail = sp_OASetProperty @MailID, 'MailFrom', @MailFromUID
EXEC @mail = sp_OASetProperty @MailID, 'FromName', @FromName
EXEC @mail = sp_OASetProperty @MailID, 'Body', @Body
EXEC @mail = sp_OASetProperty @MailID, 'Subject', @Subject
EXEC @mail = sp_OAMethod @MailID, 'AddAddress', NULL , @ToEmail , @ToName
EXEC @mail = sp_OASetProperty @MailID, 'IsHTML', 1
EXEC @mail = sp_OASetProperty @MailID, 'Username', @MailFromUID
EXEC @mail = sp_OASetProperty @MailID, 'Password', @MailFromPWD
EXEC @mail = sp_OAMethod @MailID, 'Send'
EXEC @mail = sp_OADestroy @MailID

So what is the data type of the ceEmailContent column of the tblCampaignEmails table? If it is NVARCHAR, then the maximum length is 4000. If NTEXT, then I wonder if perhaps CONVERT would make a difference.

Since you're not specifying the owner of the table in your query, double-check if there are multiple tables under that name with different owners, and perhaps a different data type for the ceEmailContent column.

Data type of the ceEmailContent column is ntext (16).

Right now I am using dbo owner for all objects of my database. I checked my database contains only one table with name: tblCampaignEmails

Thanks,

Haresh

Could not replicate the problem.

Is ceCampaignEmailID the primary key of the tblCampaignEmails table, or does it have a unique constraint? If not, then you are perhaps reading the content from a different row than what you're expecting.

ceCampaignEmailID is a primary key.

What's the result of this script:

declare @Body varchar(8000)
select @Body = cast(ceEmailContent as varchar(8000)) from tblCampaignEmails where ceCampaignEmailID = 72

SELECT LEN(@Body)

select DATA_LENGTH(ceEmailContent) from tblCampaignEmails where ceCampaignEmailID = 72

SELECT LEN(@Body) ------- 4501

select DATA_LENGTH(ceEmailContent) from tblCampaignEmails where ceCampaignEmailID = 72 -------- 9002

Sorry DATALENGTH instead of DATA_LENGTH

(Sorry about the underscore.)

If you query for the DATALENGTH of an NTEXT column in a table, or of an NVARCHAR variable, the result is twice the number of characters (unicode takes up two positions for a single character).

If you query for the LEN of an NVARCHAR variable, you will get the number of characters.

For an NTEXT column, you can only use the DATALENGTH function, so you have to divide the results by two to get the actual number of characters in the string.

Yes that's why my letter content contains 4501 characters and last 501 characters are truncating. I am an able to understand why?

Looking at sp_OASetProperty in Books Online, there isn't much you can find.

Following up from there to "Data Type Conversions Using OLE Automation Stored Procedures", it more or less says that OLE differentiates between strings with up to 4000 characters, and strings with more than 4000 characters.

You don't seem to have much control through the syntax at this end, but perhaps if you can set the property for the body text before any of the other properties, OLE is more likely to expect a long string. Not sure if you can change the order in which you set the properties, though ...