Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2005
    Posts
    240

    Unanswered: Stored Procedure DBMail Problems

    I'm currently having issues trying to get a stored procedure working to send mail via dbmail.

    Debugging through the stored procedure I've located the problem, which is building a string for the body of the message.

    I'm trying to build a string list based on the data in a table before sending the e-mail, but it doesn't store the string in the variable I have declared.

    Here is the procedure.

    Code:
        DECLARE @course_name nvarchar(100), @delivery_date nvarchar(4000), @delegate_name nvarchar(80), @book_comp_name nvarchar(200), @msg nvarchar(755), @msg_lines nvarchar(2000)
    
        -- Insert statements for procedure here
        DECLARE crm_cursor CURSOR FOR
        SELECT     TOP (100) PERCENT course_name, delivery_date, delegate_name, book_comp_name
        FROM         dbo.mis_EPOC_automation
        WHERE     (delivery_date > DATEADD(mm, - 6, CAST(MONTH(GETDATE()) AS VARCHAR(2)) + '/01/' + CAST(YEAR(GETDATE()) AS VARCHAR(4)))) AND 
                              (delivery_date < DATEADD(mm, - 5, CAST(MONTH(GETDATE()) AS VARCHAR(2)) + '/01/' + CAST(YEAR(GETDATE()) AS VARCHAR(4))))
        ORDER BY delivery_date DESC
    
        OPEN crm_cursor
    
        -- Get the values from the query and store them in the variables
        FETCH NEXT FROM crm_cursor INTO @course_name, @delivery_date, @delegate_name, @book_comp_name
        WHILE @@FETCH_STATUS = 0
        BEGIN
            BEGIN
                SET @msg_lines = @msg_lines + ',' + @course_name + ',' + @delivery_date + ',' + @delegate_name + ',' + @book_comp_name + CHAR(13) + CHAR(10)
            END
    
            -- Get values from next row
            FETCH NEXT FROM crm_cursor INTO @course_name, @delivery_date, @delegate_name, @book_comp_name
        END
        CLOSE crm_cursor
    
        DEALLOCATE crm_cursor
    
            -- Set the e-mail message
            SET @msg = 'There is a new set of courses that were run approx 6 months ago that require an EPOC.' + CHAR(13) + CHAR(10) +
                        'These courses were:' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) +
                      @msg_lines + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) +
                      'Regards,' + CHAR(13) + CHAR(10) +
                      'The MIS Team' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) +
                      'P.S. THIS IS AN AUTOMATED E-MAIL, PLEASE DO NOT RESPOND'
    
                -- Send the e-mail
                EXEC msdb.dbo.sp_send_dbmail
                @recipients = 'someone@somewhere.com',
                @subject = 'You have new courses in the CRM which require an EPOC!',
                @body = @msg;
    The line SET @msg_lines is the line to try and build the string list, which doesn't work.

    Is there another way to do this in a stored procedure?

    Thanks
    <- Hides behind a rock.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by KevCB226 View Post
    The line SET @msg_lines is the line to try and build the string list, which doesn't work.
    Please define "Doesn't work".
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2005
    Posts
    240
    When I debug the procedure there is no text string defined in the variable @msg_lines.

    When I check the procedure for errors there are none. But when I run the procedure I receive an e-mail with a subject, but the body of the message is blank.
    <- Hides behind a rock.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You know that:
    Code:
    SET @msg = 'Something' + NULL
    elicits NULL, yes?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Sep 2005
    Posts
    240
    OK so I changed the procedure to check for NULL, but it still sends a blank message.

    Code:
        DECLARE @course_name nvarchar(100), @delivery_date nvarchar(4000), @delegate_name nvarchar(80), @book_comp_name nvarchar(200), @msg nvarchar(755), @msg_lines nvarchar(2000)
    
        -- Insert statements for procedure here
        DECLARE crm_cursor CURSOR FOR
        SELECT     TOP (100) PERCENT course_name, delivery_date, delegate_name, book_comp_name
        FROM         dbo.mis_EPOC_automation
        WHERE     (delivery_date > DATEADD(mm, - 6, CAST(MONTH(GETDATE()) AS VARCHAR(2)) + '/01/' + CAST(YEAR(GETDATE()) AS VARCHAR(4)))) AND 
                              (delivery_date < DATEADD(mm, - 5, CAST(MONTH(GETDATE()) AS VARCHAR(2)) + '/01/' + CAST(YEAR(GETDATE()) AS VARCHAR(4))))
        ORDER BY delivery_date DESC
    
        OPEN crm_cursor
    
        -- Get the values from the query and store them in the variables
        FETCH NEXT FROM crm_cursor INTO @course_name, @delivery_date, @delegate_name, @book_comp_name
        WHILE @@FETCH_STATUS = 0
        BEGIN
            BEGIN
                SET @msg_lines = @msg_lines + ',' + @course_name + ',' + @delivery_date + ',' + @delegate_name + ',' + (CASE WHEN @book_comp_name IS NOT NULL THEN @book_comp_name ELSE '' END) + CHAR(13) + CHAR(10)
            END
    
            -- Get values from next row
            FETCH NEXT FROM crm_cursor INTO @course_name, @delivery_date, @delegate_name, @book_comp_name
        END
        CLOSE crm_cursor
    
        DEALLOCATE crm_cursor
    
            -- Set the e-mail message
            SET @msg = 'There is a new set of courses that were run approx 6 months ago that require an EPOC.' + CHAR(13) + CHAR(10) +
                        'These courses were:' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) +
                      @msg_lines + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) +
                      'Regards,' + CHAR(13) + CHAR(10) +
                      'The MIS Team' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) +
                      'P.S. THIS IS AN AUTOMATED E-MAIL, PLEASE DO NOT RESPOND'
    
                -- Send the e-mail
                EXEC msdb.dbo.sp_send_dbmail
                @recipients = 'someone@somewhere.com',
                @subject = 'You have new courses in the CRM which require an EPOC!',
                @body = @msg;
    <- Hides behind a rock.

  6. #6
    Join Date
    Oct 2003
    Posts
    60
    set @msg_lines = '' before your loop?

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes - @msg_lines is NULL initially. Also, don't use CASE....END, just use COALESCE
    Code:
    SET @msg_lines = COALESCE(@msg_lines, '') + ',' + COALESCE(@course_name, '') + ',' + @delivery_date + ',' + COALESCE(@delegate_name, '') + ',' + COALESCE(@book_comp_name, '') + CHAR(13) + CHAR(10)
    Also, what data type is @delivery_date?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Sep 2005
    Posts
    240
    Thanks, all in working order now
    <- Hides behind a rock.

Posting Permissions

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