Send bulk email from SQL Server 2008

Question

Hi, I am relatively new to databases,so please forgive me if my query sounds trivial. I need to send bulk email to all the contacts listed in a particular table. The body of the email is stored in a .doc file which begins as Dear Mr. _. The SQL server will read this file, append the last name of the contact in the blank space and send it to the corresponding email address for that last name. Any thoughts on how to proceed with this?

Any insights,suggestions,tips will be greatly appreciated.

Thanks!

Answer 1

Database = data base. Storage of data. Please dont make your poor database send out thousands of emails. This is not waht it is made for.

Write an application that does this. Multi threaded. Using data from the database and storing results / status there.

Answer 2

I don't advise using the database to send spam, but you can send e-mails via the database using:

EXEC msdb.dbo.sp_send_dbmail ...

I'm not sure how this impact your actual database performance (SELECT/INSERT/UPDATE/DELETE) but that is not my call.

http://technet.microsoft.com/en-us/library/ms190307.aspx

sp_send_dbmail [ [ @profile_name = ] 'profile_name' ]
    [ , [ @recipients = ] 'recipients [ ; ...n ]' ]
    [ , [ @copy_recipients = ] 'copy_recipient [ ; ...n ]' ]
    [ , [ @blind_copy_recipients = ] 'blind_copy_recipient [ ; ...n ]' ]
    [ , [ @subject = ] 'subject' ] 
    [ , [ @body = ] 'body' ] 
    [ , [ @body_format = ] 'body_format' ]
    [ , [ @importance = ] 'importance' ]
    [ , [ @sensitivity = ] 'sensitivity' ]
    [ , [ @file_attachments = ] 'attachment [ ; ...n ]' ]
    [ , [ @query = ] 'query' ]
    [ , [ @execute_query_database = ] 'execute_query_database' ]
    [ , [ @attach_query_result_as_file = ] attach_query_result_as_file ]
    [ , [ @query_attachment_filename = ] query_attachment_filename ]
    [ , [ @query_result_header = ] query_result_header ]
    [ , [ @query_result_width = ] query_result_width ]
    [ , [ @query_result_separator = ] 'query_result_separator' ]
    [ , [ @exclude_query_output = ] exclude_query_output ]
    [ , [ @append_query_error = ] append_query_error ]
    [ , [ @query_no_truncate = ] query_no_truncate ]
    [ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]

I'm not sure how you can alter the .DOC file from SQL, so you might have to run this as a mail merge from Word, using SQl Server as a data source:

http://msdn.microsoft.com/en-us/library/aa140183%28office.10%29.aspx

Answer 3

SQL Server is not a mail server.

You could export the information and use an external provider to send the bulk mail (if there are several thousand mails to send) or write an application that reads the data, replaces the placeholders with the values and sends the mail using your companies SMTP server. It'll be about 200 lines of code in C#.NET so it's quite trivial.

Answer 4

I've used the SQLAnswersMail product for SQL Server 2000 in the past and it offers a lot of features like mail merging.

Yes, SQL Server is a database, but I have found it to be ideal to host the data and schedule jobs to generate emails (we did not run a mail relay on the SQL Server itself). It also does a good job at sending custom query results to recipients.

I'm not sure if it's been obsoleted by all the improved email handling introduced with SQL Server 2005. A major requirement which SQL Server 2000 couldn't handle with the old MAPI-based profiles was to be able to send from multiple email addresses.