SQL Server Performance

xp_send mail

Discussion in 'General Developer Questions' started by Daveyboy, Apr 29, 2004.

  1. Daveyboy New Member

    Hi guys,

    This is probably very easy but I just can't do it!

    I am trying to append the month name (would really like previous month but having enough trouble as it is!) to an email being sent by sql. Here is what I have

    Declare @getmonth varchar(50)
    set @getmonth = 'SELECT DATENAME(month, getdate())'

    exec xp_sendmail
    @recipients = '***@***.***',
    @subject = 'Your Report for the month of ' + exec (@getmonth),
    @query = 'dbo.Monthly_email',
    @message = 'Your report is attached!'
    @dbuse = '****',
    @attach_results = 'TRUE'

    and I get:
    Server: Msg 170, Level 15, State 1, Line 9
    Line 9: Incorrect syntax near '+'.
    Server: Msg 170, Level 15, State 1, Line 9
    Line 9: Incorrect syntax near ','.

    Everything was fine until I started trying to add the month!

    Thanks in advance!



    Regards,

    Dave
  2. Luis Martin Moderator

    @recipients = '***@***.***',
    @subject = 'Your Report for the month of ' + exec (@getmonth),
    @query = 'dbo.Monthly_email',
    @message = 'Your report is attached!'
    @dbuse = '****',
    @attach_results = 'TRUE'

    Don´t yo miss a comma after @message = 'Your report is attached!'?

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  3. Daveyboy New Member

    Yes, sorry you are right, there should me a comma there, but that still doesn't solve the problem on concantenating the @subject line and the month name!

    Regards,

    Dave
  4. derrickleggett New Member

    Declare
    @getmonth varchar(50),
    @subject varchar(55)
    select @getmonth = DATENAME(month, getdate())
    select @subject = 'Your Report for the month of ' + @getmonth

    exec xp_sendmail
    @recipients = '***@***.***',
    @subject ,
    @query = 'dbo.Monthly_email',
    @message = 'Your report is attached!',
    @dbuse = '****',
    @attach_results = 'TRUE'


    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  5. Daveyboy New Member

    Had to make a small amendment for it to work:

    @recipients = '***@***.***',
    @subject = @subject,
    @query = 'dbo.Monthly_email',

    Thanks for your help guys.


    Regards,

    Dave

Share This Page