Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2014
    Posts
    2

    Post Unanswered: msdb.dbo.sp_send_dbmail not sending query result in body of email

    hello i am trying to send email to users about their claims but my code is sending blank email . can anybody help .

    code is

    BEGIN

    SET NOCOUNT ON;
    declare @Area nvarchar(255)
    declare @CAmount nvarchar(255)
    declare @TravelAmount nvarchar(255)
    declare @EmailSentTo varchar(50)


    declare cursor_1 CURSOR FOR

    SELECT Area, SUM(CAmount) AS CAmount, SUM(TravelAmount) AS TravelAmount, EMail
    FROM dbo.SPENDING

    GROUP BY Area, EMail

    ORDER BY EMail

    OPEN cursor_1

    FETCH NEXT FROM cursor_1 INTO

    @Area,
    @CAmount,
    @TravelAmount,
    @EmailSentTo


    WHILE @@FETCH_STATUS = 0


    BEGIN
    DECLARE @tableHTML NVARCHAR(MAX) ;
    declare @Email NVARCHAR(MAX) ;

    SET @tableHTML =
    N'<H2>Your Amount IS</H2>' +
    N'<table border="1">' +
    N'<tr><th>Amount</th><th>Travel Amount</th>' +

    CAST ( ( SELECT td = [Amount],'',
    td = [Travel Amount], ''




    FROM dbo.SPENDING
    where @email=email

    FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +


    N'</table>'


    PRINT @tableHTML

    exec

    msdb.dbo.sp_send_dbmail

    @profile_name = 'HDB1',
    @recipients=
    @EmailSentTo,
    @subject = 'AMOUNT Status',
    @body = @tableHTML;





    FETCH NEXT FROM cursor_1 INTO

    @Area,
    @CAmount,
    @TravelAmount,
    @EmailSentTo
    END

    close cursor_1
    deallocate cursor_1

    END

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Without any test data, we can only guess.

    My first guess is that there are two columns with the same td name in your sub-select and I'm pretty sure that isn't permitted by the FOR XML clause.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    For the emails that result in a blank email, does the query return any values, or just null?

  4. #4
    Join Date
    Oct 2014
    Posts
    2
    ALL the emails sent are blank . the query values are not null .

Posting Permissions

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