Post reply

T-SQL for thousands of HTML emails: best way(s) to store/reference image?

  • January 16, 2015 at 10:01 am

    #313755

    Hello

    I'm using sp_send_cdosysmail in HTML format while hosting the image(logo) as a regular web image to reference in the HTML email for now as shown below as:

    N'<img src="http://......./icons2/mtaBlk_ver3.png">

    <code>

    SET @line0 = ' <html><head>' + N'<img src="http://......./icons2/mtaBlk_ver3.png">

    ' + '

    ' + '<style>' + 'TD {border-right: 1px solid #aabcfe;border-left: 1px solid #aabcfe;border-bottom: 1px solid #aabcfe;color: #669;}' + 'TH {font-size: 15px;font-weight: normal;background: #b9c9fe;border-right: 2px solid #9baff1;border-left: 2px solid #9baff1;' + 'border-bottom: 2px solid 9baff1;color: #039;}' + '</style>' + '</head>' + '<body>';

    </code>

    Is it more efficient for the database I/O to reference an external image via a URL or

    storing it as a BLOB or some other method for up to thousands of email batches?

    Any ideas or suggestions would be appreciated

    Sincerely

    George

  • January 16, 2015 at 11:15 am

    #1770843

    it would be more efficient to refer to an image that exists on the internet.

    the size of the email is reduced, and also the throughput to your email server.

    I know with async emails using sp_send_dbMail, sending 2000+ emails typically took an hour or more to finally finish sending everything.

    there's also an excellent chance that your company and mail server will be flagged for spamming large number sof emails by comcast,aol or gmail servers. then you may never be able to reach your target audience again, unless you change servers.

    a long time ago i gave up that direction and starting using services like MailChimp and Constant Contact instead of rolling my own.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 16, 2015 at 12:06 pm

    #1770854

    Thanks for your reply.

    We have a special email account & carrier for this - we are a government agency also.

    I first suggested using VB.NET and BCC'ing all the emails together but they

    insisted to send them and flag them one by one instead.

    I'll suggest Mailchimp, etc. to my boss for the end-users but again its database driven and the code is entirely in TSQL

    George

Viewing 3 posts - 1 through 2 (of 2 total)

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