SQL Server Performance

more than 4000 characters truncated in mail

Discussion in 'General Developer Questions' started by haresh, Jan 16, 2007.

  1. haresh New Member

    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
  2. Adriaan New Member

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

    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.
  4. Adriaan New Member

    What command do you use for "sending"?
  5. haresh New Member

    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
  6. Adriaan New Member

    I would guess the aspEmail component is the most likely cause of the truncation.
  7. haresh New Member

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

    What data type does the @Body variable have?
  9. haresh New Member

    @Body varchar(8000)
  10. haresh New Member

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

    Thanks,

    Haresh
  11. haresh New Member

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

    Thanks,

    Haresh
  12. Adriaan New Member

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

    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
  14. Adriaan New Member

    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.
  15. haresh New Member

    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
  16. Adriaan New Member

    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.
  17. haresh New Member

    ceCampaignEmailID is a primary key.
  18. Adriaan New Member

    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
  19. haresh New Member

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

    select DATA_LENGTH(ceEmailContent) from tblCampaignEmails where ceCampaignEmailID = 72 -------- 9002
  20. haresh New Member

    Sorry DATALENGTH instead of DATA_LENGTH
  21. Adriaan New Member

    (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.
  22. haresh New Member

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

    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 ...

Share This Page