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 Thursday, June 12, 2003 2:09 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, November 17, 2014 9:18 AM
Points: 2,912, Visits: 1,841
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
Post #19928
Posted Friday, February 18, 2005 10:12 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, July 12, 2014 6:44 AM
Points: 441, Visits: 1,799

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."
Post #162749
Posted Tuesday, April 4, 2006 7:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 30, 2012 1:17 PM
Points: 29, 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.




Post #270847
Posted Wednesday, July 26, 2006 9:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 3, 2009 8:58 AM
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

 

Post #297485
Posted Friday, October 3, 2008 4:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 7, 2009 12:23 AM
Points: 10, 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.
Post #580161
Posted Friday, October 3, 2008 5:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 7, 2009 12:23 AM
Points: 10, 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
Post #580187
Posted Monday, October 6, 2008 3:51 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, July 12, 2014 6:44 AM
Points: 441, Visits: 1,799
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."
Post #580977
Posted Monday, October 6, 2008 9:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:10 PM
Points: 7,152, Visits: 15,621
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?
Post #581136
Posted Monday, February 2, 2009 8:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 22, 2013 5:06 AM
Points: 10, Visits: 57
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 !!
Post #648090
Posted Monday, February 2, 2009 2:31 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, July 12, 2014 6:44 AM
Points: 441, Visits: 1,799
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."
Post #648393
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse