May 13, 2002 at 9:52 am
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 = 350
Also, 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
May 13, 2002 at 10:52 pm
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.
Regards
Uday
February 12, 2003 at 8:51 am
Check out my problem:
http://www.sqlservercentral.com/forum/link.asp?TOPIC_ID=9937
Is there anyway to get the cursor to continue once xp_sendmail has encountered an invalid email account?
February 13, 2003 at 9:41 am
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!
June 11, 2003 at 11:38 am
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?
June 12, 2003 at 2:09 am
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.
February 18, 2005 at 10:12 am
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
April 4, 2006 at 7:40 am
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.
July 26, 2006 at 9:21 am
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
October 3, 2008 at 4:39 am
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.
October 3, 2008 at 5:23 am
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
October 6, 2008 at 3:51 am
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
October 6, 2008 at 9:00 am
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?
February 2, 2009 at 8:52 am
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 !!
February 2, 2009 at 2:31 pm
This is a guess, but is Outlook installed on the server? And if so, is it configured correctly?
Dave
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply