Sending HTML Newsletters in a Batch Using SQL Server

Question

Sending HTML Newsletters in a Batch Using SQL Server

Viewing 15 posts - 31 through 44 (of 44 total)

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

Rob Fisk SSCommitted Points: 1861 More actions · Answer 1

Mike-1076638 (5/27/2010)
select * from subscribers where subscription_date=getdate()-365
Easy Peasy
Mike
DB Software Laboratory
ETL Tools for everybody

Well, that was my point with the purely fixed distribution list on the mailserver. I was explaining where this kind of solution is highly useful.

[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]

Gift Peddie SSC Guru Points: 73570 More actions · Answer 2

This then runs at a particular interval as a scheduled task and I keep our Exchange Admin from breathing down my neck.

This relates to what I was saying about Enterprise restrictions these Exchange admins have created business for Google because Google let us run Asp.net System.NET code when Enterprise Exchange Admins and Hotmail were blocking our code. Today Google GMail pro is used by many small businesses. Microsoft is fighting back Exchange online Admins are Microsoft employees.

Kind regards,
Gift Peddie

Rob Fisk SSCommitted Points: 1861 More actions · Answer 3

Gift Peddie (5/27/2010)
This then runs at a particular interval as a scheduled task and I keep our Exchange Admin from breathing down my neck.
This relates to what I was saying about Enterprise restrictions these Exchange admins have created business for Google because Google let us run Asp.net System.NET code when Enterprise Exchange Admins and Hotmail were blocking our code. Today Google GMail pro is used by many small businesses. Microsoft is fighting back Exchange online Admins are Microsoft employees.

I dunno, I can run any code I want. If I was to take my perfectly acceptable sql solution, export the already generated email list to another application/server to sort out and then manage the interim code, I sure as hell would want it in house rather than rely on 3rd party.

OK, google lets you run ASP code. So, your SQL job that is already run via SQL to generate other info, rather than using built in SQL Server mail functionality will what? Generate a temp table of recipients in time for a separate sheduled ASP task to read the table and communicate with google to create a sender list to send a mail?

Technically speaking I can actually create a temporary mailing group in exchange directly from SQL server but why would I want to.

"Also, on the exchange admin breathing down my neck" issue, that is only going to be a problem with small businesses who can't afford <exagerate>16 processor, quad core 64GB Exchange servers and TB leased lines</exagerate>. I know for a fact that paying for hardware, OS and Software it is a hell of a lot cheaper for us to do it in house with MS Exchange even over 2 years than it is to go corporate hosted.

Again though, that is off topic for this post. If you are sending a lot of emails via SQL Server, and the mail server balks a bit here is one suggestion as to how to batch it.

I also submitted an alternative batching solution. It may bo good, it may not be.

It would be nice to see more comments on the actual area of toppic (how to batch it)

I have not yet had a chance to do tests on the code for performance and look at it as a whole but intend to. I submitted my version as an alternative but it may be awful in comparison. It certainly has the flaw that the batch size is limited by an unknown amount depending on average email length in batch.

It's a good article, Let's discuss the article rather than the mirriad ways of sending email.

[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]

Gift Peddie SSC Guru Points: 73570 More actions · Answer 4

but what if it is user subscription, that chan change minute to minute, that generates the recipient list so each new user has the email address as part of the user table in sql. What if also you need a query to pick the recipients, for instance all who have signed up for over a year, ot any criteria you want.
You can have a job to check if any citeria match then generate the emails based on matching criteria all within a single SQL job without having to pass the recipient list out to another process.
Keeping it within SQL server then reduces the number of platforms that need to be maintained.

I did not say the article is not a good article but I am saying DB mail comes with paid SQL Server editions but .NET code runs in all editions. And yes all subscriptions can be automated using SQL Server Express and Windows scheduler as needed.

Kind regards,
Gift Peddie

Paul Thornett Right there with Babe Points: 790 More actions · Answer 5

select ...

union

select ...

union

select ...

union

select ...

This is a ridiculous construct! If you are certain you do want to use union because you want to sort and deduplicate the rows, then you should always use this construct instead:

select ...

union all

select ...

union all

select ...

union

select ...

Mike Chamberlain SSC Enthusiast Points: 169 More actions · Answer 6

This article tells you "how", but my question would be "why?". Why would you even want to do this?

This is a horrendous solution. It is a sequence of ugly hacks, and almost every step of the article has something that makes me cringe.

SQL Server is a database engine, not some general purpose application programming environment. Use the database to store and manipulate data. Leave the higher level stuff like constructing HTML documents and sending emails to a general purpose programming language like C#.

People who are commenting that this is a beautiful solution do not know the meaning of beauty.

Satnam Singh Hall of Fame Points: 3542 More actions · Answer 7

Mike,

In my case, the table storing the email addresses of the users to whom the Newsletters were supposed to be sent was in MYSQL database.It is also not possible that the organisations will be creating a seperate relay server for sending Newsletters.It is also possible that the organisation might be using the same relay server for this purpose as well as for the corporates internal use. If yes, then sending all the Newsletters in a bulk sometimes creates a heavy load.

Satnam

Mike Chamberlain SSC Enthusiast Points: 169 More actions · Answer 8

My question as to "why" is not "why would you want to send in batches" (the case for this is obvious, and you made it well), but "why would you want to do it all *in SQL Server*". My criticism is of the architecture, not the idea. Use the DB for data operations. Use an application for higher level operations. Don't force it all into the DB. Just because you can do it in SQL Server, doesn't mean that you should.

I understand that on some occasions it is sometimes necessary to hack together something that works, but is not necessarily beautiful, and I am as guilty as anyone of this. The difference is, I wouldn't write an article and post it on the internet telling everyone about it.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 9

Good article but I guess I'm missing something. Instead of sending things in batches of "x" quantity every 10 minutes, wouldn't it just be easier to send 1 email every 3 or 4 seconds?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Simon G Ten Centuries Points: 1285 More actions · Answer 10

Good solution. We have used a very similar method for a while to send out large mailing lists on a daily basis. It's very quick.

Also, we send out in the thousands via an Agent Job, 1 at a time with a slight pause in between and don't get any issues.

Mike C SSC-Insane Points: 23224 More actions · Answer 11

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 SSCommitted Points: 1861 More actions · Answer 12

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.

[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]

VinicioAizpurua SSC Veteran Points: 272 More actions · Answer 13

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 SSCommitted Points: 1861 More actions · Answer 14

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.

[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 15

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.