Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

stocked procedure to send mail

Status
Not open for further replies.

wawer

Programmer
Aug 24, 2001
13
FR
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.
 

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.
 
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 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.
 
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
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top