Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345

Sending HTML Newsletters in a Batch Using SQL Server Expand / Collapse
Author
Message
Posted Friday, May 28, 2010 9:48 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 15, 2014 5:20 PM
Points: 1,276, Visits: 1,134
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
Post #929799
Posted Monday, May 31, 2010 6:56 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 22, 2014 8:32 AM
Points: 163, 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.
Post #930358
Posted Thursday, June 3, 2010 6:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 20, 2012 1:38 PM
Points: 40, 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
Post #932056
Posted Thursday, June 3, 2010 7:31 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 22, 2014 8:32 AM
Points: 163, 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.
Post #932125
Posted Thursday, June 3, 2010 7:59 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 21, 2014 12:08 PM
Points: 1,414, Visits: 4,540
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.


https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #932148
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse