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 123»»»

Using Xp_sendmail With the Recipients Generated From a Query Expand / Collapse
Author
Message
Posted Tuesday, July 10, 2001 12:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 21, 2004 12:15 AM
Points: 11, Visits: 1
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/kdas/xp_sendmail.asp


Post #559
Posted Monday, July 16, 2001 4:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 23, 2003 12:00 AM
Points: 1, Visits: 1
I had been looking for an article like this. Thank you. I still have the problem that sql2000 doesn't seem to like to send through Outlook express for instance. Is there a workaround for this or another very minimal other mail client that might work or is there a way to send straight to smtp server? Could there be a followup article about connecting the entire thing together?

Thanks, Bo




Post #19910
Posted Monday, July 16, 2001 4:51 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:19 PM
Points: 2,891, Visits: 1,781
Here is the same thing avoiding CURSORs.

Use Test
GO
Declare @MyRecipients nvarchar (255)
Declare @MyMessage nvarchar (255)
Set @MyRecipients=''
Set @MyMessage = ‘Your timesheet is overdue, please send it ASAP.'
WHILE @MyRecipients IS NOT NULL
BEGIN
SELECT @MyRecipients = MIN ( email_address )
FROM Test_Table
WHERE enail_Address > @MyRecipients AND Status = 0

IF @MyRecipients IS NOT NULL
BEGIN
Print @MyRecipients
Exec Master.dbo.xp_sendmail @MyRecipients, @MyMessage
END
END



LinkedIn Profile
Newbie on www.simple-talk.com
Post #19911
Posted Monday, July 16, 2001 9:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 21, 2004 12:15 AM
Points: 11, Visits: 1
Thx for that code David, I might actually change mine ;)...I wanted to avoid cursors, but it was the first thing I could think of..bad habits die hard...

As to Bo's question, I read an article, I can't quite remember where, but someone had an article posted on how to use Outlook Express and Third Party mailers with xp_Sendmail..I'll see if I can find it.

Real Programmers don't comment their code. If it was hard to write it should be hard to understand.



Post #19909
Posted Tuesday, July 24, 2001 2:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 21, 2005 9:07 AM
Points: 4, Visits: 1
Very good information. I have done this before but never quite so eloquently.



====================
"Awful tired now, Boss. Dog tired."
http://www.dwacon.com



====================
"Awful tired now, Boss. Dog tired."
http://www.dwacon.com
Post #19912
Posted Tuesday, January 29, 2002 8:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 19, 2005 4:24 PM
Points: 1, Visits: 1
An excellent tip

Rex



Rex
Post #19913
Posted Monday, February 11, 2002 4:14 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 22, 2011 9:03 AM
Points: 171, Visits: 16
What about when the email name you are retrieving from a table is no longer valid? Any ideas on error processing?

Can't quite get this part to work properly, or it works okay until the 1st illegal name is encountered then "rolls back". Then retries x number of times (something you define) and then sends the emails out again until that first illegal address is encountered. Wish xp_sendmail had error processing and was documented better.

To make a long story short, if you try to process email addresses in a table within a proc, it can't roll back xp_sendmail operations and if you have set retry option up then you get duplicate emails sent to some humorless people!




Post #19914
Posted Monday, March 11, 2002 8:43 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 10, 2009 4:18 PM
Points: 1,287, Visits: 11
I used the same code that's in the article for a recent project, and it works great. However, I noticed in the code that the first recipient will not receive the email.

1. This is because after you fetch their email address, you then check the FETCH_STATUS to make sure it's valid.
2. You then print the email address and fetch a new one.
3. Then the email is sent.

It seems that you would want to send the email before fetching the next record. Otherwise, someone will be left out.

Jason




Post #19915
Posted Tuesday, April 23, 2002 7:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 30, 2002 12:00 AM
Points: 1, Visits: 1
Good thought provoking article, got some new ideas on e-mail and cursors.




Post #19916
Posted Monday, May 6, 2002 8:15 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 10, 2003 12:00 AM
Points: 10, Visits: 1
Do you have to have SQL Mail set up to use xp_sendmail?


Post #19917
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse