Possible to pass many records to sp_send_dbmail without using a loop?

Question

Possible to pass many records to sp_send_dbmail without using a loop?

Maxer

SSCrazy Eights

Points: 8964
More actions
May 25, 2010 at 11:29 am

#225062

I need to use database mail in SQL Server 2008 to send out various notifications.
We have a table that already exists in a database and various programs we have will add rows when they need a message emailed to someone (i.e. job completed, some reminder, scripted server task failed, or alerting some clients that they need to update their customer information, etc...)
I will need to query this table, gather up what I need, and then it appears I will have to call the sproc RBAR for each email that needs to go out.
Is there any way to instead feed that sproc the results of a query?
Perhaps a query that runs and returns 100 records at a time, and passes the results to the sproc?
I assume the answer to this is NO, and I will have to write a loop using a cursor, but if there is any way to avoid doing that I would be very happy to do it.
Thanks!

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

Maxthere is not a ton of options,as you've identified;

the parameter @recipients,@copy_recipients and @blind_copy_recipients for sp_send_dbmail is a varchar max, expecting a semicolon seperated list of recipients;

if all the recipients can be sent together, where they see the other persons emails, you could aggregate the named with FOR XML, or BCC everyone;

so you could aggregate on a per-message basis to limit some of the looping.

otherwise, it's some sort of a loop construct, whether cursor,loop or building the list of commands to execute.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

Maxer (5/25/2010)
I need to use database mail in SQL Server 2008 to send out various notifications.
We have a table that already exists in a database and various programs we have will add rows when they need a message emailed to someone (i.e. job completed, some reminder, scripted server task failed, or alerting some clients that they need to update their customer information, etc...)
I will need to query this table, gather up what I need, and then it appears I will have to call the sproc RBAR for each email that needs to go out.
Is there any way to instead feed that sproc the results of a query?
Perhaps a query that runs and returns 100 records at a time, and passes the results to the sproc?
I assume the answer to this is NO, and I will have to write a loop using a cursor, but if there is any way to avoid doing that I would be very happy to do it.
Thanks!

I agree... there's no way that I know of to send 100 rows at a time to the email system. However, you can make it appear so... create a table to send the email rows to and have a permanent job running that processes the rows on a FIFO basis. Yep... that permanent job is going to be RBAR but that's because they designed it that way and there's not a whole lot you can do about that. You just don't need to punish other code for that bit of lacking functionality.

--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)

Maxer SSCrazy Eights Points: 8964 More actions · Answer 3

How does database mail work anyway?

It's an external application right?

It runs on some sort of timer or something and its job is to take what I pass it, store it in some MSDB tables and send off "stuff" to the smtp server I specify when I create the account and assign it to a profile....

Maybe I can cheat the system and push data directly into whatever tables database mail itself uses?

I don't suppose there is a "back door" as elegant as that available?

I'm grasping at straws, I just hate cursors and RBAR... so very wrong... makes me want to take a shower... my hands will never be clean again!

lol

EDIT:

http://msdn.microsoft.com/en-us/library/ms189635.aspx

So, it dumps its data into the mail queue... somewhere in msdb...

Though, even if I do track down how to do that... that is likely crazy, right?

Say it was simple as pie, and that this mail queue was setup with a column for each parameter that database mail had, so a simple INSERT would get the job done.

Even if that WERE the case, a service pack down the road, that entire table structure could change and since I bypassed sp_send_dbmail to get there... I would be SOL...

hmm...