Create loop to send out emails

  • 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

  • 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...

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

  • I'm getting smarter finally!!!

    Took long enough!

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

  • 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.

  • 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

  • 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

  • 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

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

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