Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Create loop to send out emails Expand / Collapse
Author
Message
Posted Friday, October 4, 2013 1:23 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, May 9, 2014 11:26 AM
Points: 54, Visits: 109
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
Post #1501748
Posted Friday, October 4, 2013 4:55 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 12:45 AM
Points: 736, Visits: 4,719
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...
Post #1501787
Posted Friday, October 4, 2013 5:00 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:35 AM
Points: 36,977, Visits: 31,494
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1501790
Posted Saturday, October 5, 2013 5:46 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 12:45 AM
Points: 736, Visits: 4,719
I'm getting smarter finally!!!


Took long enough!
Post #1501879
Posted Saturday, October 5, 2013 7:50 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:35 AM
Points: 36,977, Visits: 31,494
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1501885
Posted Monday, October 7, 2013 6:50 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, May 9, 2014 11:26 AM
Points: 54, Visits: 109
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.
Post #1502111
Posted Monday, October 7, 2013 8:44 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, August 21, 2014 9:40 AM
Points: 984, Visits: 1,326
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
Post #1502179
Posted Monday, October 7, 2013 8:55 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, May 9, 2014 11:26 AM
Points: 54, Visits: 109
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
Post #1502186
Posted Monday, October 7, 2013 9:09 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, August 21, 2014 9:40 AM
Points: 984, Visits: 1,326
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
Post #1502201
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse