Pulling out emails from SQL Server 2008 database

I need to send a email to all my subscribers. I am currently on a shared hosting plan that offers a SQL Server 2008 database.

I really don't want to copy all the emails into my mail client (thunderbird) nor do I want to make a progr开发者_StackOverflow社区am that would grab all the emails from the db and send it through smtp.net

I am looking for a free solution that I could just tell it the db path and the column of the emails and will allow me to write a subject and body and let me send it.

1. Configure database mail with a profile that has the from / reply-to information matching what you want your recipients to see. You can see one tutorial here:

• Setting up Database Mail for SQL Server

2. Depending on whether you want to send the same message to everyone, or personalize each one, you can use a loop to either build up chunks of addresses to BCC (you won't necessarily want to send to all the recipients at once) or to craft the individualized body/subject for each recipient.

3. Once you have a body and recipient(s) in each iteration of the loop, you can use msdb.dbo.sp_send_dbmail to send each message.

EDIT adding some sample code.

Setup:

USE tempdb;
GO

CREATE TABLE dbo.Emails(Email VARCHAR(320));

INSERT dbo.Emails VALUES('a@b.com'),('b@c.com'),('c@d.com');

If you want to just send all the same message and assuming the list is short - batching or chunking would be a different question:

DECLARE @bcc VARCHAR(MAX) = '';

SELECT @bcc += ';' + Email FROM dbo.Emails;

SET @bcc = STUFF(@bcc,1,1,'');

SELECT @bcc;

/*
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'profile name',
    @recipients = 'your address',
    @blind_copy_recipients = @bcc,
    @body = N'body',
    @subject = N'subject';
*/

-- If you want to send an individual message to each recipient, use a cursor:

DECLARE @e VARCHAR(320);

DECLARE e CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY
    FOR SELECT Email FROM dbo.Emails;

OPEN e;

FETCH NEXT FROM e INTO @e;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Sending to ' + @e;

    /*
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'profile name',
        @recipients = @e,
        @body = N'body',
        @subject = N'subject';
    */

    FETCH NEXT FROM e INTO @e;
END

CLOSE e;
DEALLOCATE e;

Cleanup:

DROP TABLE dbo.Emails;

Finally, if your list of recipients is large, I'll suggest you seek other alternatives that are better equipped for bulk mailing.

ASP.net has an SMTP client class. I'm sure you could select all the email addresses from your table then loop through them, calling the SMTP Send() method on each address.