Hello,

I have a stored proc that fetches records and emails them.
Inside this stored proc I call another stored proc: "getMailContent"
which has an out parameter @content

Sp_GetMailcontent @ReminderName,@ReminderDate,@dy,@Footer,@content output

Problem:

Inside Sp_GetMailcontent, @content has a hard code(fixed) text as well as a dynamic variable @footer which is appended to it.
The footer itsef is 7500 chars long and @content exceeds 9000 chars.

In the parent Storede Proc the @content is passed in another Stored Proc used to email this content.

sp_SendEmails @fromemail',@toemail,@subject,@content

How to send the complete email content in the email procedure, as it itself is limited by a 8000 chars ?
I get a stripped down email.

Regards,

Vinay

Vinay

It would be helpful to know what is your underlying procedure for sending email, xp_sendmail (which uses MAPI) and requires Outlook to be installed on the server, or is it the extended stored proc create by Gert Draper that just allows you to send directly through SMTP?

Finding or writing a better xp would be one way. For now I will assume you are using xp_sendmail

Two approaches
1) Write your message to a file and attach it to the email
xp_sendmail @recipients = '[email protected] '
    ,@message = 'See attached file' -- limited to 8000
    ,@attachments = 'c:\MyMessage.txt' -- no limit
    ,@subject = 'subject'

2) Send your message as a query (either in the body or as an attachment)
xp_sendmail @recipients = '[email protected]'
    ,@query = 'SELECT Header + Message + Footer FROM EmailMessages' -- Table you create where you store your info
    ,@subject = 'subject'
    ,@attach_results = 'true'


David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com

Hi David,

Thanks for the information. I am using the Stored Proc that sends directly through smtp; i.e using "sp_OASetProperty" and "sp_OAMethod".

Can I send attachments also using these ?

Thanks in advance.

Vinay

Yes you can, if it was implemented in the Stored Proc. However, stored procs that call sp_OA procs are going to be far less efficient than an XP where you make one call to it.
Take a look at http://www.sqldev.net/xp/xpsmtp.htm
You don't need outlook. You can send right to an SMTP server

David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com