Results 1 to 3 of 3

Thread: Xp_sendmail

  1. #1
    Join Date
    Jun 2002
    Posts
    19

    Unanswered: Xp_sendmail

    I have a query that works on its own, however when I put
    it into xp_sendmail it fails. It appears that the "set"
    command does not work. Here is the query. Any suggestions?
    Can you use variables within xp_sendmail @ Query section?

    EXEC master.dbo.xp_sendmail
    @recipients = 'Richard Peoples',
    @subject = 'The following Budget Checked items need
    attention.',
    @query ='DECLARE @A1 CHAR (20)
    DECLARE @A2 CHAR (2)
    SET @A1 = 'Yvette Palomo'
    SET @A2 = 'N'
    IF (select COUNT (*)
    from FSPROD75.dbo.PS_C_JRNLLN_BUDCHK
    where RETURN_TO_ANALYST = @A1 AND
    BUDGET_CHECK_CLEAR= @A2) > 0
    begin
    select A.JOURNAL_ID, A.PROJECT_ID, A.ACCOUNT,
    B.XLATLONGNAME from FSPROD75.dbo.PS_C_JRNLLN_BUDCHK A
    INNER JOIN FSPROD75.dbo.XLATTABLE B ON A.BCM_LINE_STATUS =
    B.FIELDVALUE
    where (((A.RETURN_TO_ANALYST)= @A1) AND
    ((A.BUDGET_CHECK_CLEAR)=@A2)) AND (B.FIELDNAME
    = "BCM_LINE_STATUS")
    end'

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    I think you were missing some quotation marks. try:

    Code:
    EXEC master.dbo.xp_sendmail 
         @recipients = 'Richard Peoples', 
         @subject = 'The following Budget Checked items need attention.',
         @query ='DECLARE @A1 CHAR (20)
                        , @A2 CHAR (2)
                  SET @A1 = ''Yvette Palomo''
                  SET @A2 = ''N''
    
                  -- or
    
                  select @A1 = ''Yvette Palomo''
                       , @A2 = ''N''
    
                  IF (select COUNT (*) from FSPROD75.dbo.PS_C_JRNLLN_BUDCHK
                             where RETURN_TO_ANALYST = @A1 
                               AND BUDGET_CHECK_CLEAR= @A2) > 0 begin 
                    select A.JOURNAL_ID
                         , A.PROJECT_ID
                         , A.ACCOUNT
                         , B.XLATLONGNAME 
                      from FSPROD75.dbo.PS_C_JRNLLN_BUDCHK A 
                     INNER JOIN FSPROD75.dbo.XLATTABLE B ON A.BCM_LINE_STATUS = B.FIELDVALUE
                     where A.RETURN_TO_ANALYST = @A1
                       AND A.BUDGET_CHECK_CLEAR = @A2 
                       AND B.FIELDNAME = ''BCM_LINE_STATUS''
                  end'
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Jun 2002
    Posts
    19
    That worked, thanks!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •