March 27, 2012 at 12:46 pm
I use the msdb system stored procedure [msdb].[dbo.sp_send_dbmail] to send mail. Basically I'm using a cursor and I'm changing the arguments (to, from, subject, etc) before I'm calling this stored procedure.
Is this the "best" way to mass email through SQL Server or is there something I should try?
Thanks!
-Kevin
March 27, 2012 at 12:55 pm
i do it the same way, strictly because I'm sending individualized emails; the typical "Dear [Firstname]" emails to each email address i get from multiple rows in a table.
Additionally, i need the output parameter form sending the mail so i can later track failures, bad email addresses, etc. that would not be easily available otherwise.
there's a few different ways to do that, but they all will require either a loop / cursor, or require that you create a varchar(max) string, populated with concatenated commands , so you can execute a suite of commands in a single EXECUTE(@longstring);
the varchar(max) method is for those who absolutely avoid cursors, but for me, cursors are fine for specific processes like sending mail or doing maintenance where you have to hit the metadata to get database names and such.
Lowell
March 27, 2012 at 1:57 pm
For alot of cases Cursors are fine.
If you are sending the same email multiple times then you might be better off concatenating all TO (or CC if you want to maintain anonymity) addresses (using a delimiter like ';') and let your Exchange Server (or whatever you are using for mail server) handle sending multiple emails out. This will reduce some of the pressure on the SQL mail profile queue, but you loose the fine error logging (and mail delivery accountability) from the SQL side, you can still chase status through Exchange Server.
SQL Mail is still pretty low on features, in that you don't get a native implementation of features like templating or finer layout controls etc. But thats because its not really supposed to be used as a full-on mail management application.
-----------------
... Then again, I could be totally wrong! Check the answer.
Check out posting guidelines here for faster more precise answers[/url].
I believe in Codd
... and Thinknook is my Chamber of Understanding
March 27, 2012 at 2:52 pm
I like Grasshoppers idea if you sending the same email to multiple recipients. However, if you are sending a lot of emails too many recipients and the email address, subject and body have the potential to be different then you might want to try storing your emails in a table and then loop through the table to load your variables and execute the sp_send_dbmail proc for each pass then you can still track the message status from the sysmail_mailitems table if need be. I have done this with great success sending hundreds of emails at a time.
Here is my sample code.
DECLARE @PROFILE_NAME AS VARCHAR(25)
DECLARE @RECIPIENTS AS VARCHAR(MAX)
DECLARE @SUBJECT AS VARCHAR(MAX)
DECLARE @BODY AS VARCHAR(MAX)
DECLARE @CurrentRec AS INT
DECLARE @RecCount AS INT
SET @CurrentRec = 1
SET @RecCount = 0
SELECT @RecCount = COUNT([RecordID]) FROM [dbo].[EmailMessageTable]
WHILE @RecCount >= @CurrentRec
BEGIN
SELECT @PROFILE_NAME=COALESCE(PROFILE_NAME,''),@RECIPIENTS=COALESCE(RECIPIENTS,''),@SUBJECT=COALESCE([SUBJECT],''),@BODY=COALESCE(BODY,'') FROM [dbo].[EmailMessageTable] WHERE RecordID = @CurrentRec
--SELECT @PROFILE_NAME,@RECIPIENTS,@SUBJECT,@BODY
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @PROFILE_NAME,
@recipients = @RECIPIENTS,
@subject = @SUBJECT,
@body = @body
SET @CurrentRec = @CurrentRec + 1
END
March 29, 2012 at 10:16 am
thanks for the responses everyone
March 29, 2012 at 10:33 am
if this helps anyone, or generate suggestions for my process, great!
this is exactly how i do it.
in my case, i need an individualized email for each recipient...no emails with mulitple email addresses iun the TO: and no BCC stuff either. We want one email per email address.
We send well formed HTML emails, and the email body has place holders for things like [FirstName],[PhoneNumber], etc, so they can be swapped out and individualized. Depending on the email purpose, it might have place holders for [LastEventAttended], [LastProduct] or whatever we decide we need.
that html gets inserted into a table with a varchar(max) field after it's been reviewed and had htmlTidy run against it.
Images in this example email would external fully qualified http: links , but we've done it via attachments and the src="cid:image.png" method as well for self contained images.
here's the typical code we've used in the past:
--Email Campaign.
declare @HtmlModel varchar(max)
declare @HtmlBody varchar(max)
SELECT @HtmlModel = EmailBody
FROM EmailCampaigns
WHERE ID = 1
--a different process will have created an awesome looking well formed html with our known
--placeholders for [FirstName] [LastName] [Phone] [Email] that might ber substituted for individualization
--PRINT @HtmlModel;
SET @HtmlBody=''
declare
@mailID int,
@id int,
@first varchar(64),
@Last varchar(64),
@phone varchar(64),
@email varchar(64)
declare c1 cursor for
--################################
SELECT ID,
ISNULL(FirstName,'Friend') AS FirstName,
ISNULL(LastName,'') AS LastName,
ISNULL(Phone,''),
ISNULL(Email ,'')
--the row_number is so that if i put your name in the database five times, you only get a single email.
FROM (
select ROW_NUMBER() over (partition by email order by email,len(firstname)desc,len(lastname)desc ) AS RW, *
from RawContacts
where email <> '') x
WHERE RW = 1
--this WHERe stays in place until we are ready to go LIVE with the email.
and email IN('lowell@somedomain.com','otherReviewer@somedomain.com')
--################################
open c1
fetch next from c1 into @id,@first,@last,@phone,@email
While @@fetch_status <> -1
begin
SET @HtmlBody = REPLACE(@HTMLModel,'[FirstName]',@first)
SET @HtmlBody = REPLACE(@HtmlBody,'[LastName]', @Last)
SET @HtmlBody = REPLACE(@HtmlBody,'[Phone]', @phone)
SET @HtmlBody = REPLACE(@HtmlBody,'[Email]', @email)
--EXEC msdb.dbo.sp_send_dbmail
@Profile_name = 'Database Mail Profile Name',
@recipients=@email,
@subject = 'Our non profits Call for Volunteers',
@body = @HtmlBody,
@body_format = 'HTML',
@mailitem_id = @mailID OUTPUT
--@body_format = 'TEXT'
INSERT INTO CampaignRecipients(Campaign,RawContactID,MailSentID,MailSentDate)
SELECT 1,@id,@mailID,GETDATE()
fetch next from c1 into @id,@first,@last,@phone,@email
end
close c1
deallocate c1
GO
Lowell
December 31, 2014 at 6:31 am
Can this be done using SQL Server 2000? I have an urgent task that needs to send out a reminder email to up to 40 individuals two business days before an event they are registered for.
Can I use the information in this thread to do this in SQL Server 2000?
December 31, 2014 at 8:01 am
I feel for you, locked into older versions that you have no control over.
Bending this process to your will, you are going to have boatloads of issues: varchar max didn't exist in SQL2000, i think, so large emails are off limits.
sending mail required the old syncronous process that used DBMail .
Do you already have dbmail set up?
alternatively, can the 2000 server talk to a 2005 or above? this article shows how to make 2000 use the 2005 services via a job:
http://www.sqlservercentral.com/articles/SQL+Server+2000/69475/
vice versa, can a 2005 server pull data fromt eh 2000 server? then you cna use the example above, after you've set up 2005 mail.
Lowell
January 16, 2015 at 10:24 am
I have the same task and used sp_send_cdosysmail for HTML emails not so much customized but more of as a group related to the customers interests (up to thousands at a time)
I need to include our firms logo and right now I'm hosting it on a website and referencing it that way. [ N'<img src="http:......../icons2/mtaBlk_ver3.png"> ]
Is it more efficient to store as a BLOB ( SQL Server 2008 R2) or some other method.
SET @line0 = ' <html><head>' + N'<img src="http:......../icons2/mtaBlk_ver3.png">
' + '<p> </p>' + '<style>' + 'TD {border-right: 1px solid #aabcfe;border-left: 1px solid #aabcfe;border-bottom: 1px solid #aabcfe;color: #669;}' + 'TH {font-size: 15px;font-weight: normal;background: #b9c9fe;border-right: 2px solid #9baff1;border-left: 2px solid #9baff1;' + 'border-bottom: 2px solid 9baff1;color: #039;}' + '</style>' + '</head>'
+ '<body>';
any ideas, suggestions, experiences would be appreciated
Thanks
George
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply