SQLServerCentral / Discuss Content Posted by Kunal Das / Article Discussions / Article Discussions by Author / Using Xp_sendmail With the Recipients Generated From a Query / Latest Posts

RE: Using Xp_sendmail With the Recipients Generated From a Query

David Jackson — Mon, 02 Feb 2009 14:31:15 GMT

This is a guess, but is Outlook installed on the server? And if so, is it configured correctly?Dave

RE: Using Xp_sendmail With the Recipients Generated From a Query

igsri — Mon, 02 Feb 2009 08:52:56 GMT

Hi David;I am using the same to send Mail from SQL Server 2000But i am getting this error message: xp_sendmail: failed with mail error 0x80040111Could you please help me out in this case , i need the solutionThanks !!

RE: Using Xp_sendmail With the Recipients Generated From a Query

Matt Miller (#4) — Mon, 06 Oct 2008 09:00:27 GMT

In 2005, xp_sendmail should be avoided. It's been replaced by sp_sendDBMail, which tends to operate much better (it's SMTP-based, so doesn't required an outlook client to be installed and configured; it also doesn't wait for the e-mail to complete, so your SQL server doesn't have to become dependent on where Exchange Server is up or down....) It's not on by default, so you need to use the Surface Area Config tool to enable it.

RE: Using Xp_sendmail With the Recipients Generated From a Query

David Jackson — Mon, 06 Oct 2008 03:51:58 GMT

This is written for, and tested on SQL 2000 I'm afraid. I do not have 2005 so I can't help you there. I assume the xp_sendmail parameters have changed. Or another thought, out of the box in SQL 2005 is xp_sendmail disabled? If so, have you enabled it?HTHDave J

RE: Using Xp_sendmail With the Recipients Generated From a Query

mayurs — Fri, 03 Oct 2008 05:23:33 GMT

Hi, Thanks for the such a wonderful script.I run your script, its working fine, but mail haven't send to any one.I think There could be extra setting needs to do for the xp_sendmail.Would you Please email, what are the Pre-requities to send a mail from Sql server 2005.my mail id is mayurs@winsoftech.com.I appreciate your time and help.Thanking you.regards,mayur

RE: Using Xp_sendmail With the Recipients Generated From a Query

mayurs — Fri, 03 Oct 2008 04:39:49 GMT

Hi david, Thanks for the good script.But after executing script, I got an error message.it is " xp_sendmail: Procedure expects parameter @user, which was not supplied. "help me to overcome from it.Thanking you.mayur.

RE: Using Xp_sendmail With the Recipients Generated From a Query

Michael Weiss — Wed, 26 Jul 2006 09:21:00 GMT

My code is below, my issue is when this executes, lets say the query brings back 2 email address, instead of sending one to each name it send 2 to each name. How/where can I fix this??

CREATE PROCEDURE usp_email_ccb

DECLARE @TITLE VARCHAR(500)DECLARE @DESCRIPTION VARCHAR(500)DECLARE @REQUIREMENTS VARCHAR(500)Declare @MyRecipients nvarchar (255)Declare @NEWMessage nvarchar (255)Declare @NEWSubject nvarchar (255)DECLARE @RECORD_NUM NCHAR(2)Declare MyCursor Cursor For

Select LOGINID From USERS Where TASK='CCB'

Open MyCursor

Fetch Next From MyCursor Into @MyRecipients

While @@Fetch_Status = 0

BEGIN

Print @MyRecipients

Fetch Next From MyCursor Into @MyRecipients

select @record_num=[id], @title=[title], @description=[description], @REQUIREMENTS=[REQUIREMENTS] from display a,(select max(id) mxid from display) b where a.id=b.mxid

SET @NEWSUBJECT='YOU HAVE AN IT REQUEST TO APPROVE!!! THIS IS A TEST EMAIL FOR THE WAP TOOL, JUST DISREGARD!!!'SET @NEWMESSAGE='TITLE: '+@TITLE + " " + +CHAR(13)+ +CHAR(13)+ 'DESCRIPTION: '+@DESCRIPTION + " " + +CHAR(13)++CHAR(13)+ 'REQUIREMENTS: '+@REQUIREMENTS + " "++CHAR(13)++CHAR(13)+ + 'Click on this link to view your request. http://localhost/WAP/db/ba2btpeb2.asp?a=GETRECORD&ID='+@RECORD_NUM

Exec Master.dbo.xp_sendmail @MyRecipients, @SUBJECT =@NEWSUBJECT, @MESSAGE=@NEWMESSAGE

End

Close MyCursor

Deallocate MyCursorGO

RE: Using Xp_sendmail With the Recipients Generated From a Query

ew — Tue, 04 Apr 2006 07:40:00 GMT

How would you add the subject to the message instead of getting the default of "SQL SERVER MESSAGE"? I've tried adding the @subject = 'Test Subject' but get an error "Must pass parameter number 2 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'." I then tried to add DECLARE @MYSUBJECT nvarchar (255) and then SET @MYSUBJECT = 'Test Subject' and then

Exec Master.dbo.xp_sendmail @mysubject ,@MyRecipients, @MyMessage END END . That gives an ODBC error 170 (42000)

Any help is appreciated.

RE: Using Xp_sendmail With the Recipients Generated From a Query

David Jackson — Fri, 18 Feb 2005 10:12:00 GMT

Here's my stab at it.</* Created by free online sql formatter: http://www.wangz.net/gsqlparser/sqlpp/sqlformat.htm *//*This sets up a quick test table*/IF object_id('DJmailDetails_T') > 0 DROP TABLE djmaildetails_t CREATE TABLE djmaildetails_t ( id INT NOT NULL IDENTITY( 1 , 1 ), firstname VARCHAR(30) NOT NULL, lastname VARCHAR(30) NOT NULL, emailaddress VARCHAR(100) NOT NULL, sendmail BIT NOT NULL DEFAULT (0) )INSERT INTO djmaildetails_t (firstname, lastname, emailaddress, sendmail)VALUES ('dave', 'jackson', 'me@nonworkingExample.co.uk', 1)INSERT INTO djmaildetails_t (firstname, lastname, emailaddress, sendmail)VALUES ('fred', 'bloggs', 'you@nonworkingExample.co.uk', 1)INSERT INTO djmaildetails_t (firstname, lastname, emailaddress, sendmail)VALUES ('joe', 'smith', 'him@nonworkingExample.co.uk', 0)INSERT INTO djmaildetails_t (firstname, lastname, emailaddress, sendmail)VALUES ('mike', 'jones', 'her@nonworkingExample.co.uk', 1)/*This selects from above table*/DECLARE @recipients VARCHAR(4000)SELECT @recipients = Coalesce(@recipients + ';','') + emailaddressFROM djmaildetails_tWHERE sendmail = 1PRINT @recipients -- show what we gotPRINT '' -- a blank line/***************************************************************************************************//*The next two variables are normally set in theerror handling of each 'bit' of DML in your SP/Script*/DECLARE @Error_status INTDECLARE @Error_message VARCHAR(128)SET @Error_status = -1SET @Error_message = 'failure updating something!' --something apposite /*This should be in the error handler of the script or SP*/ ERRORHANDLER:DECLARE @sql VARCHAR(8000)DECLARE @mailMessage VARCHAR(128)DECLARE @mailSubject VARCHAR(128)DECLARE @Procedure_Name SYSNAME -- Holds the name of the currently executing procedureSET @Procedure_Name = Object_name(@@PROCID) -- Set the procedure name in the variable /*the next two lines would not be called above normally, so needs to be commented out here.declare @recipients varchar(4000)Select @recipients = COALESCE(@recipients+';' , '') + emailAddress from DJmailDetails_T where sendMail = 1*//*This sends the message*/ SET @mailMessage = 'Table update failure' --something apposite SET @mailSubject = 'Error in ' + Isnull(@Procedure_Name,'Ad-hoc Query')SET @sql = 'exec master.dbo.xp_sendmail ''' + @recipients + ''',@message = ''' + @mailMessage + ''',@query = ''select ''''' + @error_message + ''''' as ErrorMessage,' + Isnull(CAST@Error_status AS VARCHAR),' ') + ' as ErrorNumber'',@subject = ''' + @mailSubject + ''',@dbuse = ''master'',@attach_results = false,@no_output = true,@width = 8000'PRINT (@sql) --change this from print to an exec to 'really' do it.--exec (@sql) --or uncomment this line/*Script to drop the table when finished testing

if object_id('DJmailDetails_T') > 0drop table DJmailDetails_T

*/I liked this so much I posted it on my site at http://glossopian.co.ukDave J

RE: Using Xp_sendmail With the Recipients Generated From a Query

David.Poole — Thu, 12 Jun 2003 02:09:00 GMT

We have looked at sending a newsletter type HTML mail and are looking at the sp_makewebtask and sp_runwebtask stored procedures.In testing we have used these procedures to generate e-mails as files then have use xp_cmdshell to copy them to the Pickup directory of the SMTP server.

RE: Using Xp_sendmail With the Recipients Generated From a Query

jvg — Wed, 11 Jun 2003 11:38:00 GMT

Is it possible to use xp_sendmail to send html-format email? There is a message type (@type =) parameter, but where does one find info on custom message types?

RE: Using Xp_sendmail With the Recipients Generated From a Query

aliozer — Thu, 13 Feb 2003 09:41:00 GMT

quote:
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!

RE: Using Xp_sendmail With the Recipients Generated From a Query

mylesjj — Wed, 12 Feb 2003 08:51:00 GMT

Check out my problem:http://www.sqlservercentral.com/forum/link.asp?TOPIC_ID=9937Is there anyway to get the cursor to continue once xp_sendmail has encountered an invalid email account?

RE: Using Xp_sendmail With the Recipients Generated From a Query

uday — Mon, 13 May 2002 22:52:00 GMT

[quote]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?Well that's my question actually...Sql BOL says I can "start" sqlmail with the "@user" argument which basically allows me to choose a different outlook profile. Query Analyzer says succesfully started and stopped with teh new profile, but, it just does'nt happen.RegardsUday

RE: Using Xp_sendmail With the Recipients Generated From a Query

casey — Mon, 13 May 2002 09:52:00 GMT

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.Yes, if you use @attach_results = 'TRUE', @width = 350Also, just a thought, when creating the Excel spreadsheet, it's easier for users to open if you create a comma delimited file and use the .csv suffix.Casey

RE: Using Xp_sendmail With the Recipients Generated From a Query

casey — Mon, 13 May 2002 09:50:00 GMT

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.

RE: Using Xp_sendmail With the Recipients Generated From a Query

pisoft1 — Mon, 13 May 2002 08:14:00 GMT

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?

RE: Using Xp_sendmail With the Recipients Generated From a Query

uday — Thu, 09 May 2002 18:13:00 GMT

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

RE: Using Xp_sendmail With the Recipients Generated From a Query

inkntation — Thu, 09 May 2002 12:38:00 GMT

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.

RE: Using Xp_sendmail With the Recipients Generated From a Query

Jason Delaune — Mon, 06 May 2002 22:10:00 GMT

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

RE: Using Xp_sendmail With the Recipients Generated From a Query

Connie Miner — Mon, 06 May 2002 20:15:00 GMT

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

RE: Using Xp_sendmail With the Recipients Generated From a Query

vijay_kumar — Tue, 23 Apr 2002 07:00:00 GMT

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

RE: Using Xp_sendmail With the Recipients Generated From a Query

Jason Delaune — Mon, 11 Mar 2002 08:43:00 GMT

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

RE: Using Xp_sendmail With the Recipients Generated From a Query

jamyer — Mon, 11 Feb 2002 16:14:00 GMT

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!

RE: Using Xp_sendmail With the Recipients Generated From a Query

rbenemer — Tue, 29 Jan 2002 08:15:00 GMT

An excellent tipRex

RE: Using Xp_sendmail With the Recipients Generated From a Query

dwacon — Tue, 24 Jul 2001 14:10:00 GMT

Very good information. I have done this before but never quite so eloquently.===================="Awful tired now, Boss. Dog tired."http://www.dwacon.com

RE: Using Xp_sendmail With the Recipients Generated From a Query

Blasphemy — Mon, 16 Jul 2001 09:44:00 GMT

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.

RE: Using Xp_sendmail With the Recipients Generated From a Query

David.Poole — Mon, 16 Jul 2001 04:51:00 GMT

Here is the same thing avoiding CURSORs.Use Test GODeclare @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

RE: Using Xp_sendmail With the Recipients Generated From a Query

bograham — Mon, 16 Jul 2001 04:48:00 GMT

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

Using Xp_sendmail With the Recipients Generated From a Query

Blasphemy — Tue, 10 Jul 2001 00:00:00 GMT

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