Using Xp_sendmail With the Recipients Generated From a Query

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


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



  • Check out my problem:

    Is there anyway to get the cursor to continue once xp_sendmail has encountered an invalid email account?

  • 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!

  • 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?

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

  • Here's my stab at it.


    /* Created by free online sql formatter: */

    /*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





    VALUES     ('dave',




    INSERT INTO djmaildetails_t





    VALUES     ('fred',




    INSERT INTO djmaildetails_t





    VALUES     ('joe',




    INSERT INTO djmaildetails_t





    VALUES     ('mike',




    /*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*/


    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

    Dave J
    "I don't know what I don't know."

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

  • 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 @MyRecipients nvarchar (255)

    Declare @NEWMessage nvarchar (255)

    Declare @NEWSubject nvarchar (255)


    Declare MyCursor Cursor For

    Select LOGINID From USERS  Where TASK='CCB'

    Open MyCursor

    Fetch Next From MyCursor Into @MyRecipients

    While @@Fetch_Status = 0


    Print @MyRecipients

    Fetch Next From MyCursor Into @MyRecipients

    select  @record_num=[id],




     from display a,(select max(id) mxid from display) b where


    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





    Close MyCursor

    Deallocate MyCursor



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


  • 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

    I appreciate your time and help.

    Thanking you.



  • 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?


    Dave J
    "I don't know what I don't know."

  • 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?

  • 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 !!

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

    "I don't know what I don't know."

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

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