send mail from sproc
-
Hi all,
I would like to run a sproc (I have created already) and ultimately send an e-mail to the e-mail list that is generated in the returned dataset by the sproc. Anyone know if that can be done and how?
In other words, I have a sproc that looks for drivers with expired licenses, once it finds that list, it stores it it along with that drivers rep_id in a temp table.
so I have a table w/ drivers name and their rep's id. I have to then join another table (which I can do in original select, i'd like to know which method is better) which finds the e-mail address where id = id...Now i have a nice list of e-mail addresses that I'd like to e-mail (each) and tell them "the driver's license is expired please notify them"
Can I do this from a sproc?
TIA,
Chris
Aurora
-
So you are asking to send an email to list of people? If you can get the list into a var (ie @emaillist) delimited by ';' then you can use the xp_sendmail to send the mail. Of course your email list can not be longer than 8000 char
Example
EXEC master.dbo.xp_sendmail
@recipients = @emaillist,
@query = @sqlString,
@subject = 'some subject',
@message = 'Your message'
Not sure if I understood your question but hope that helps
-
You understood my question exactly right...now my question is:
TSQL wise, how would I store that list so that it can be 'looped' through...is that right?
Currently, I am storing the records/e-mail addy's in a temp table...
Ah, a caveat, I need to e-mail each ae, their prospective list or I guess quick & dirty I can mail the entire list to all ae's and then they would be responsible for finding their info on the list...More eloquent would be to e-mail particular ae their particular list...can this even be done?
tia
Chris
Aurora
-
assuming you have some mailfunctionality properly set up and working (xp_sendmail, xp_smtpmail or other), this is nothing extraordinary at all.
Just loop through your data, for each mailaddress, gather the appropriate data, put your mail together and fire it off with the mailproc at hand. While loop or cursor is ideal for this kind of work.
How it would look in detail is up to you. You need to keep track on which mails has been sent and such, but a fairly simple logtable or similar should take care of that.
It's something you have to put together yourself
/Kenneth
-
Yep, a loop would work good. Doing the ";" delimited list wouldn't be a great idea is each person is suppose to get info unique to them. A while loop with a fetch cursor as mentioned above would be ideal.
Give it a whirl post back with code example if you have troubles.
-
Thanks I'll give the loop a try!
I'll update you guys shortly!
TIA,
Christine
Aurora
-
Unless you need to customize the email for each address, as long as the email list is less than 8000 characters, then simply use the ; delimited address list and assign to xp_sendmail @blind_copy_recipients =<my addresses>
Each will not know about the other email addresses and you are only sending one message out to many people.
Andy
-
Well guys thanks for your help! This is what I've found out...Because the data is being returned in one list/table, and the recipients only need the data that's pertinent to them, I've discovered that sending out the individual result sets cannot happen unless I introduce a third party tool like SQL Answers...
I'm sure I can dig deeper and write boat loads of code but...for now I'll entertain using xp_sendmail and send the entire result set to the manager of the dept. Still open to ideas...
Thnaks again,
Christine
Aurora
-
Hmm... but you do have all the required data for each recipient avaliable in table(s), right?
I can't really see why a 3rd party tool would be necessary. It shouldn't take boatloads of code either, it still seems like a fairly trivial task within the suggested 'loop-method'.. Unless there are more to it than that?
/Kenneth
-
I apologize I should have been more clear:
Each rep should get the list that's pertinent to them, not the entire reult set...
So the loop will work if I am sending the same list to all rep's but I don't want to send them the whole list just the data that's pertinent for them.
Still open to ideas!
Thanks
Aurora
-
But you do have each reps items in a table? So... begin the loop, while in the loop for each rep:
- grab the mailaddress
- select the list pertinent to that particular rep
- put together the mail with address, subject, the list items and what more needed
- send the mail
- grab next mailaddress and repeat...
Isn't that basically what you could do?
/Kenneth
-
I would have to agree with the last post. If that doesn't work then maybe you post some test data/example that would show why it doesn't work. We're shotting for the bullseye but it's kind of dark
-
OKAY, allow me to go over the steps with you:
- I would store the results for ALL rep's in a temp table?.?
- Create another temp table that holds the email addresses that I need to loop through
- Now here's where it gets fuzzy for me (bear in mind i'm a newbie dB dev) I create the loop to go through the result set or to go through the e-mail list? Or both?
I am seeing the logic vaguely...I know I need to store the reps id's in both tables and create a select that would grab the pertinent data by id and mail it to rep where repid =repid...
am I close?
I'll work on the sql and put it out here shortly...Thanks for your help.
Chris
Aurora
-
I think you're on the right track
If we disregard such things as to have temptables or not and other 'techie' stuff, you just need to put down some basic facts before you figure out how to do it (ie what code is needed)
You need to know (for the basic SQL):
- Where to find the reps
- Where to find the reps mailaddresses
- What reason is there that a rep should be picked
- Where to find the pertinent data
- How to link that data to a certain rep
All the above may or may not be selected straight from basetables without need for temptables. It sort of depends on the 'overall solution' which is to prefer.
In addition it can be nice to think beforehand about:
- What to do with the rep and data when mail is sent succesfully
- What to do with the rep and data when mail is sent unsuccesfully
When you have the where and whens and whys sorted, I guess that the flow would go something like:
- While there exists reps that need mail sent to them; (loop start)
- Get one rep, find the rep's mailaddress, link the rep to the data and get that too
- Put together all parts (address, data etc) into the desired mail
- Send off the mail
- Check for success of failure and act accordingly (ie logging mail sent, or mail failed etc)
- Goto top and grab next rep if there is one
- Done
This gives that the 'loop needed' is on the list of reps only. As long as there are reps, you continue the loop, when there are no more, the loop is ended. You do all other steps inside the loop in sequence.
As to actual code, that depends mostly on your actual tablenames and such. There's also a few ways that the above can be implemented (ie actually written), like with or without temptables, a cursor or a while loop - details like that.
/Kenneth
-
Kenneth, you're awesome, I can actually begin to envision the code...let me work on it and I'll keep you posted on my progress...I stink at actual coding so I am sure I will have syntax issues...
In the proverbial words of CA's govenor...
"I'll be back!"
Chris
Aurora
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply