Post reply

send a .html attachment directly from SQL Server

  • October 28, 2005 at 10:52 am

    #92065

    Has anyone ever sent a .html attachment directly from SQL Server (not using .NET or anything like that)?  I can't figure out how to do it.  I found this article:  http://databasejournal.com/features/mssql/article.php/10894_3489111_1  but I can't figure out how to do an attachment.

    Anyone out there know?

  • October 28, 2005 at 12:08 pm

    #601042

    Brenda,

    I use that sp to send html formatted email documents from SQL Server but I don't send them as attached files they come in as html based emails. If that's what you're looking for I may be able to help out. I'm don't think that that sp has the ability to do attachments built into it but I could be wrong.   

  • October 28, 2005 at 12:14 pm

    #601046

    But what if your html code is over 8000 characters?  What do you do?  And how do you store your html?  In a column?  Or how?

  • October 28, 2005 at 1:47 pm

    #601079

    Brenda,

    I did a little research, because i typically don't have outputs greater than 8000 characters which does seem to be a limit for the body variable.

    I can attach existing files to an email through using the stored procedure with a few modifications, I've tested it a few times and it seems to work fine. You'd just have to have the file created on the harddrive san or whatever storage device first and know it's location to attach it. Would this help?

  • October 28, 2005 at 3:13 pm

    #601123

    Todd

    YES, that would help!  How do I do it?

  • October 28, 2005 at 4:03 pm

    #601133

    I have pulled an exerpt from the sp code. Add the lines in blue to your code in the stored procedure

    ***********************************************

    create procedure usp_send_cdosysmail

    @from varchar(500) ,

    @to varchar(500) ,

    @subject varchar(500),

    @body varchar(4000) ,

    @smtpserver varchar(25),

    @bodytype varchar(10),

    @attachment varchar(256)  

    as

    declare @imsg int

    declare @hr int

    declare @source varchar(255)

    declare @description varchar(500)

    declare @output varchar(1000)

    exec @hr = sp_oacreate 'cdo.message', @imsg out

    exec @hr = sp_oasetproperty @imsg,

    'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").value','2'

    exec @hr = sp_oasetproperty @imsg,

      'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").value',

      @smtpserver

    exec @hr = sp_oamethod @imsg, 'configuration.fields.update', null

    exec @hr = sp_oasetproperty @imsg, 'to', @to

    exec @hr = sp_oasetproperty @imsg, 'from', @from

    exec @hr = sp_oasetproperty @imsg, 'subject', @subject

    -- if you are using html e-mail, use 'htmlbody' instead of 'textbody'.

    exec @hr = sp_oasetproperty @imsg, @bodytype, @body

    exec @hr = sp_OAMethod @imsg, 'AddAttachment',null, @attachment  

    exec @hr = sp_oamethod @imsg, 'send', null

    *************************************************

    When you call the stored procedure, just call it with the additional @attachment parameter which should be declared as varchar(256) as well and assign it the path to the file as shown below

    Excerpt from calling sp.

    Declare @attachment varchar(256)

    Select @attachment = 'd:\whatever.html'

    exec usp_send_cdosysmail @from, @to, @subject, @body, @smtpserver, @bodytype, @attachment  

    Obviously you will be passing all the other parameters as well . Let me know if it works for you or if you need further assistance

     

  • October 28, 2005 at 4:17 pm

    #601136

    That is cool!  Will it always show up on the email as an attachment?  Or can you just have it view the html?  Or does that just need to go in the body part if you want it to show up without an attachment?  Am I confusing?

  • October 28, 2005 at 9:35 pm

    #601148

    Yeah, the attachments will always show up as files unless your email system has some way to have them show automatically.

    Basically if you want something to show up in the body of the email you have to put it in the body of the message which I think is limited in cdosys to 8000 characters so to go larger I think you will have to use attachments.

  • October 30, 2005 at 3:19 pm

    #601193

    Brenda, If you use xp_smtp_sendmail from http://www.sqldev.net it has an option to use a html file as the body of the email. We do this for a couple of quick and dirty admin reports.

     

    --------------------
    Colt 45 - the original point and click interface

  • October 31, 2005 at 9:23 am

    #601350

    Hi Phill

    Can you show me an example of how you do that?  I am very curious...

    Thanks!

  • October 31, 2005 at 6:45 pm

    #601457

    Brenda

    xp_smtp_sendmail has a parameter called @MessageFile, You just pass in the full path and filename to the html file and it is used as the message body. There is quite a few examples at the website.

    Generally we use sp_makewebtask to generate the html files, but one of my colleagues put together a bunch of VBScript to create a series of html reports. Then we just emailed them out using xp_smtp_sendmail.

     

    --------------------
    Colt 45 - the original point and click interface

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply