Using Xp_sendmail With the Recipients Generated From a Query

Question

Using Xp_sendmail With the Recipients Generated From a Query

Blasphemy

SSC Veteran

Points: 273
More actions
July 10, 2001 at 12:00 am

#77366

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/kdas/xp_sendmail.asp

Viewing 15 posts - 1 through 15 (of 29 total)

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

bograham Grasshopper Points: 19 More actions · Answer 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

David.Poole SSC Guru Points: 76035 More actions · Answer 2

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

LinkedIn Profile

Blasphemy SSC Veteran Points: 273 More actions · Answer 3

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.

dwacon SSC Veteran Points: 212 More actions · Answer 4

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

rbenemer Grasshopper Points: 19 More actions · Answer 5

rbenemer

Grasshopper

Points: 19

January 29, 2002 at 8:15 am

#422801

An excellent tip

Rex

jamyer Default port Points: 1433 More actions · Answer 6

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!

Jason Delaune SSCrazy Points: 2649 More actions · Answer 7

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

vijay_kumar SSC Rookie Points: 25 More actions · Answer 8

Good thought provoking article, got some new ideas on e-mail and cursors.

Connie Miner SSC Enthusiast Points: 118 More actions · Answer 9

Do you have to have SQL Mail set up to use xp_sendmail?

Jason Delaune SSCrazy Points: 2649 More actions · Answer 10

Yes, you have to have SQL Mail started prior to using xp_sendmail. You can start it through the GUI by using Enterprise Manager or you can use the T-SQL form of xp_startmail. Hope this helps. See Books Online for more specifics of xp_sendmail and xp_startmail.

Jason Delaune

inkntation Valued Member Points: 71 More actions · Answer 11

hmm... is there any way to send attachments with xp_sendmail? im working on an application for a call center that needs to generate info on a persons queue, dump them to an excel spreadsheet, and then send out the spreadsheets.

uday Say Hey Kid Points: 683 More actions · Answer 12

Guys!

First of all thankx to Kunal for sharing this with us.

Now, my problem:- I'm using SQL Mail to send out Emails to Customers re: outstanding Invoices. I also use SQL Mail on the same box to send out emails to our Sales Reps with summaries of outstanding invoices. Now, I've set up multiple outlook profiles on the server. I tried using

xp_startmail @user = 'CreditControlDept'

xp_sendmail .... (to customers)

xp_stopmail @user = 'CreditControlDept'

xp_startmail @user = 'InvoiceTracker'

xp_sendmail...(to internal sales reps)

xp_stopmail @user = 'InvoiceTracker'

DOES NOT WORK!!! the profile does'nt switch at all. SQL Mail sends out all the emails using the profile that it started with. How can I use xp_startmail and xp_stopmail to switch between different user profiles.

Regards

Uday

pisoft1 SSC Rookie Points: 33 More actions · Answer 13

I am a little confused the only way I got a mail session to work was to have a nt profile and outlook profile set up on the server then I was able to start the mail session.

How did you get a second outlook profile to work with a different name?

casey SSC Enthusiast Points: 110 More actions · Answer 14

quote:
hmm... is there any way to send attachments with xp_sendmail? im working on an application for a call center that needs to generate info on a persons queue, dump them to an excel spreadsheet, and then send out the spreadsheets.