SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using Xp_sendmail With the Recipients Generated From a Query


Using Xp_sendmail With the Recipients Generated From a Query

Author
Message
David.Poole
David.Poole
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7913 Visits: 3290
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.

LinkedIn Profile

Newbie on www.simple-talk.com
David Jackson
David Jackson
Say Hey Kid
Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)

Group: General Forum Members
Points: 661 Visits: 1913

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 + ';','') + emailaddress
FROM djmaildetails_t
WHERE sendmail = 1

PRINT @recipients -- show what we got

PRINT '' -- 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 INT

DECLARE @Error_message VARCHAR(128)

SET @Error_status = -1

SET @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 procedure
SET @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') > 0
drop table DJmailDetails_T

*/

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

Dave J




http://glossopian.co.uk/
"I don't know what I don't know."
ew
ew
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 402

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.





Michael Weiss
Michael Weiss
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 2

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

AS

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 MyCursor
GO


mayurs
mayurs
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 24
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.
mayurs
mayurs
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 24
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
David Jackson
David Jackson
Say Hey Kid
Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)

Group: General Forum Members
Points: 661 Visits: 1913
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?

HTH

Dave J


http://glossopian.co.uk/
"I don't know what I don't know."
Matt Miller (4)
Matt Miller (4)
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12685 Visits: 18584
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.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
igsri
igsri
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 58
Hi David;

I am using the same to send Mail from SQL Server 2000

But i am getting this error message: xp_sendmail: failed with mail error 0x80040111


Could you please help me out in this case , i need the solution



Thanks !!
David Jackson
David Jackson
Say Hey Kid
Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)

Group: General Forum Members
Points: 661 Visits: 1913
This is a guess, but is Outlook installed on the server? And if so, is it configured correctly?

Dave


http://glossopian.co.uk/
"I don't know what I don't know."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search