Create loop to send out emails

Question

Create loop to send out emails

Stubby Bunny

SSCommitted

Points: 1733
More actions
October 4, 2013 at 1:23 pm

#283985

What I need to do is create a loop that loops through the data to produce an email to send out. the following is sample data. I need to do it in a way to send one email to a person istead of in the case below up to 3. I want to sumarize the informaiton then send out email. I could do with a cursor easy but I am dealing with thousands of records and a cursor would have negative performance on tables. My attempt would be to pick the issuedID first then put in a cursor and loop around the records conatating to a temp table then selecting from here. Just wna to know if there is an easier way to do what I want. Thanks
ISSUEDID EMAIL OVERDUE DtISsued ST ITem
3631 john.doe@mail1682013/04/19DG5N4G3 DELL PRECISION T3500
4324 john.doe2@mail382013/08/27CWJ0181145Y EPSON PROJECTOR EMP 50
4413 hjane.doe@mail1612013/04/26JDKXVV4 DELL PRECISION T3500
4413 hjane.doe@mail1612013/04/262Z1BLN5 DELL LATITUDE D630
4413 hjane.doe@mail1612013/04/2610Z2VJ6 DELL LATITUDE E6400
13581 henry.doe@mail2462013/01/31125R4L4 DELL LATITUDE E6400

Viewing 9 posts - 1 through 8 (of 8 total)

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

pietlinden SSC Guru Points: 63506 More actions · Answer 1

Maybe someone more knowledgeable than I will chime in, but what if you did this with a cursor and scheduled it as a job? (so it runs in off hours). Or you could use Service Broker to make the mailing asynchronous. Not sure there's a way around using a cursor, though. Maybe Dwain knows...

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

There are plenty of ways around using a cursor for this... it's just not necessary to find one because THIS type of task is precisely what Cursors were designed to do. A nice "firehose" cursor would do nicely and consume very few resources.

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

pietlinden SSC Guru Points: 63506 More actions · Answer 3

pietlinden

SSC Guru

Points: 63506

October 5, 2013 at 5:46 pm

#1656129

I'm getting smarter finally!!!

Took long enough!

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

Stubby Bunny (10/4/2013)
What I need to do is create a loop that loops through the data to produce an email to send out. the following is sample data. I need to do it in a way to send one email to a person istead of in the case below up to 3. I want to sumarize the informaiton then send out email. I could do with a cursor easy but I am dealing with thousands of records and a cursor would have negative performance on tables. My attempt would be to pick the issuedID first then put in a cursor and loop around the records conatating to a temp table then selecting from here. Just wna to know if there is an easier way to do what I want. Thanks
ISSUEDID EMAIL OVERDUE DtISsued ST ITem
3631 john.doe@mail1682013/04/19DG5N4G3 DELL PRECISION T3500
4324 john.doe2@mail382013/08/27CWJ0181145Y EPSON PROJECTOR EMP 50
4413 hjane.doe@mail1612013/04/26JDKXVV4 DELL PRECISION T3500
4413 hjane.doe@mail1612013/04/262Z1BLN5 DELL LATITUDE D630
4413 hjane.doe@mail1612013/04/2610Z2VJ6 DELL LATITUDE E6400
13581 henry.doe@mail2462013/01/31125R4L4 DELL LATITUDE E6400

If you'll put the data in a "readily consumable format", I'll be happy to help. Please see the first "Helpful Link" in my signature line below for how to do that. Thanks.

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

Stubby Bunny SSCommitted Points: 1733 More actions · Answer 5

All,

Ok if you are saying a cursor is the best way to do this i'm on board, I know how to do them all quite well just getting tired of when I used to post them getting yelled at for using them. Thanks for the advice gents, have a great day.

Kurt W. Zimmerman SSCrazy Eights Points: 8676 More actions · Answer 6

Stubby Bunny (10/7/2013)
All,
Ok if you are saying a cursor is the best way to do this i'm on board, I know how to do them all quite well just getting tired of when I used to post them getting yelled at for using them. Thanks for the advice gents, have a great day.

As Jeff pointed out a CURSOR is going to be the best way to handle this, or at least the easiest. If it bothers you to use a CURSOR then consider using a WHILE loop to process the emails. This way you are not rendering the CURSOR and perform the same task.

Keep in mind that if CURSORS were such taboo then at some point the wizards at Microsoft would obsolete the construct and no longer make it available.

Kurt

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman

Stubby Bunny SSCommitted Points: 1733 More actions · Answer 7

Hi Kurt,

Thanks for the info, I dont mind using cursors to do things as such. Until i found this forum (WAY BETTER and more expertise) I was given hell by members of other forums for using cursors and everyone would always insist i use temp tables with While loops whch area also not effiecient either. Just glad to have found a forum where members have some knowledge. Thanks

Kurt W. Zimmerman SSCrazy Eights Points: 8676 More actions · Answer 8

Stubby Bunny (10/7/2013)
Hi Kurt,
Thanks for the info, I dont mind using cursors to do things as such. Until i found this forum (WAY BETTER and more expertise) I was given hell by members of other forums for using cursors and everyone would always insist i use temp tables with While loops whch area also not effiecient either. Just glad to have found a forum where members have some knowledge. Thanks

Keep in mind that there is more than 1 way to skin that cat.... If you want to resort using SQL Server for the email component then you have only a few choices.

You can do the same using SSIS, PowerShell, VB Script or any other language of choice. My previous employer had a developer that liked to use Cold Fusion for stuff like this. He had several email jobs that would send out really nice HTML emails embedding things like a list of items that was purchased (Order Confirmation Email).

But if you simply need to get the job done, with nothing fancy, and spinning through a cursor does the job, DO IT!

Kurt

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman