stocked procedure to send mail

[wawer]

Hi all,
I'm using ASPEmail component inside a stocked procedure (on SQL server 7.0) in order to send to clients. What's surprising is that all the mails sent by this sp have a limited length: 255 characters. I've verified all parameters, they are varchar type and set to 1000. I don't understand why the mail length is limited. Have you any idea?

Thank you for your help.

 

[tlbroadbent]

Please explain what you mean by "ASPEmail component." I assume you meant "stored procedure when you typed "stocked procedure." How do you call the ASPEmail component? Are you using the extended stored procedure, xp_sendmail, to send messages from SQL Server? A litle more detail would be helpful.

Thanks, Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[wawer]

So sorry with my poor english. I'm a french speaking and I've translated "procédure stockée" from French to english and the result was so bad. so sorry.
ASPEmail is a .dll you can find out at

http://www.aspemail.com.

It's free and it send mail quite correctly from any application (in my case: from SQL server sp). I've written a sp in which I used sp_OACreate to call ASPEmail and to set parameters like that:
CREATE procedure sp_SendMail
@To varchar(1000),
@From varchar(1000),
@FromName varchar(1000),
@Message varchar(1000),
@Message_txt varchar(1000),
@Subject varchar(1000)
AS

DECLARE @Mail int
DECLARE @hr int
DECLARE @src varchar(1000), @desc varchar(1000)

EXEC @hr = sp_OACreate 'Persits.MailSender', @Mail OUT

IF @hr <> 0

BEGIN
EXEC sp_OAGetErrorInfo @Mail, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END

EXEC sp_Insert @texte_insert = @Message

EXEC @hr = sp_OASetProperty @Mail, 'IsHTML', 1

EXEC @hr = sp_OASetProperty @Mail, 'Host', '62.160.102.1'
EXEC @hr = sp_OASetProperty @Mail, 'From', @From
EXEC @hr = sp_OASetProperty @Mail, 'FromName', @FromName
EXEC @hr = sp_OASetProperty @Mail, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @Mail, 'Body', @Message
EXEC @hr = sp_OASetProperty @Mail, 'AltBody', @Message
EXEC @hr = sp_OAMethod @Mail, 'AddAddress', NULL, @To
EXEC @hr = sp_OAMethod @Mail, 'Send()'


IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @Mail, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END

Set @hr = 0

The problem is that the message length is limited to 255 characters.
Thank you for your helps.

 

[tlbroadbent]

Are you running SQL Server 7? There is a bug in SQL 7 according to the Microsoft Knowledgebase article at the following link.

sp_OASetProperty Truncates Varchar Types to 255 Characters

http://support.microsoft.com/support/kb/articles/Q278/4/48.ASP

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[wawer]

What a nice surprise!
Yes, I'm running SQL Server 7.0 and I ignore completly this bug. Thank you so much for the information.
I think I'll try to send all the mails in text format in order to economize the 255 precious characters.
Thank you.