SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Create loop to send out emails


Create loop to send out emails

Author
Message
Stubby Bunny
Stubby Bunny
Old Hand
Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)

Group: General Forum Members
Points: 359 Visits: 151
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@mail 168 2013/04/19 DG5N4G3 DELL PRECISION T3500
4324 john.doe2@mail 38 2013/08/27 CWJ0181145Y EPSON PROJECTOR EMP 50
4413 hjane.doe@mail 161 2013/04/26 JDKXVV4 DELL PRECISION T3500
4413 hjane.doe@mail 161 2013/04/26 2Z1BLN5 DELL LATITUDE D630
4413 hjane.doe@mail 161 2013/04/26 10Z2VJ6 DELL LATITUDE E6400
13581 henry.doe@mail 246 2013/01/31 125R4L4 DELL LATITUDE E6400
pietlinden
pietlinden
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13946 Visits: 14123
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
Jeff Moden
SSC Guru
SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)

Group: General Forum Members
Points: 213295 Visits: 41977
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
pietlinden
pietlinden
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13946 Visits: 14123
I'm getting smarter finally!!!


Took long enough!
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)

Group: General Forum Members
Points: 213295 Visits: 41977
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@mail 168 2013/04/19 DG5N4G3 DELL PRECISION T3500
4324 john.doe2@mail 38 2013/08/27 CWJ0181145Y EPSON PROJECTOR EMP 50
4413 hjane.doe@mail 161 2013/04/26 JDKXVV4 DELL PRECISION T3500
4413 hjane.doe@mail 161 2013/04/26 2Z1BLN5 DELL LATITUDE D630
4413 hjane.doe@mail 161 2013/04/26 10Z2VJ6 DELL LATITUDE E6400
13581 henry.doe@mail 246 2013/01/31 125R4L4 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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Stubby Bunny
Stubby Bunny
Old Hand
Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)

Group: General Forum Members
Points: 359 Visits: 151
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
Kurt W. Zimmerman
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2604 Visits: 1400
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
Stubby Bunny
Old Hand
Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)

Group: General Forum Members
Points: 359 Visits: 151
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
Kurt W. Zimmerman
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2604 Visits: 1400
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search