SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sending HTML Newsletters in a Batch Using SQL Server


Sending HTML Newsletters in a Batch Using SQL Server

Author
Message
Mike C
Mike C
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6573 Visits: 1172
Interesting article. Apart from automating delivery of error messages and alerts, I've never considered using SQL Server as a mailing list server. I'm trying to think of a scenario where I'd choose this method over the other possibilities. If I had a massive mailing list, as mentioned in the article, I'd probably use a dedicated mail server. Could be I'm just not thinking about it hard enough... maybe someone out there has some scenarios where this would be a superior method?

Thanks
Mike C
Rob Fisk
Rob Fisk
SSC-Addicted
SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)

Group: General Forum Members
Points: 435 Visits: 428
I have many scenarios for using SQL server to send the mails. None of mine yet have used a batching method since the recipient list is pretty small.

1. Data integrity checks.

I have several scripts which run daily and check data entered into the system for common errors that cannot wholly be caught by front end field checking (or are from a 3rd party source that does not have it).
The DB job runs a set of data checks for DBs at each department and if a check comes back with results then a warning email is sent to the relevant members.

2. Event based mails.
Another set of jobs I have involve a complex set of queries to determine pending events requiring action which mail the relevant user if any events are flagged for them.

3. Daily IT dashboard.
I have a job which checks all of the active jobs on all of our database and mails us if any have failed. of course if the job checking job fails...

Newsletter generation is something that will be bought into play later and given that no-one in the company has more than a smattering of .net knowledge the chances are that it may well be SQL server generated and that I will use some form of batching.
Saying that though I may well have the initial query return name and salutation as well as email address and send one at a time personalising the content of each mail

I can see though, that with the skill sets present it would be just as easy to pass a query or run an SP in via .net that returns a result set and then, if there are results, run another to retrieve recipients but for me it is far easier to do everything in the one place.

_______________________________________________________
Change is inevitable... Except from a vending machine.

VinicioAizpurua
VinicioAizpurua
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 115
Nice solution regardless the comment why to do it with SQL. It is all depends on you organization and the resources you have in your hands. Sometimes you have to deal with certain resources and that all you have. In other hand about the solution presented, it would be nice to present how did you confiugured the SQL mail profile in terms SMPT relay. By adding this you will have a complete solution for those that would be in the same situation for sending massive mails. About the answer about the creatio of mailing list, once again it is all depend on who owns the process and the data. Same comment about .Net solution. You will need to have the data available somewhere. In other words, this is a solution presented for database administrators/developers. I am considered included in this last group and that's why I see this is a viable solution.

Best regads

Vinicio Aizpurua
Miami, FL
Rob Fisk
Rob Fisk
SSC-Addicted
SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)

Group: General Forum Members
Points: 435 Visits: 428
I did touch on that subject that in http://www.sqlservercentral.com/Forums/FindPost928963.aspx

Generally authenticated users can relay and setting up the profile to use authentication is fairly straightforward as I remember.

For my profile I do not and so I had to add the IP address of my SQL server to a list of IPs allowed to relay within the exchange server settings. I didn't go into details there since it will change between vendor and version of mail server.

_______________________________________________________
Change is inevitable... Except from a vending machine.

alen teplitsky
alen teplitsky
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7224 Visits: 4674
VinicioAizpurua (6/3/2010)
Nice solution regardless the comment why to do it with SQL. It is all depends on you organization and the resources you have in your hands. Sometimes you have to deal with certain resources and that all you have. In other hand about the solution presented, it would be nice to present how did you confiugured the SQL mail profile in terms SMPT relay. By adding this you will have a complete solution for those that would be in the same situation for sending massive mails. About the answer about the creatio of mailing list, once again it is all depend on who owns the process and the data. Same comment about .Net solution. You will need to have the data available somewhere. In other words, this is a solution presented for database administrators/developers. I am considered included in this last group and that's why I see this is a viable solution.

Best regads

Vinicio Aizpurua
Miami, FL


it's good security policy to limit who can send SMTP traffic outside the organization to one server. i've seen where people would set up unofficial wifi access points with no security and connect them to the company LAN. Spammers pick up on this and start sending millions of emails via the wifi and people's local SMTP server installations that are unprotected. developers are notorious for doing this. next thing you know your organization ends up on a lot of the email blacklists and other organizations start to reject your emails.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search