Longest recipient list in JOB

  • Hi

    I have one job which sends mails to recipients. if i send mail through that job to few users like 2..4 users then it is working fiine but if i add all users (recipients count can be around 50) in recipients list, it is not working.

    job doesnt show any error( successful run ) but in Database mail log i got below

    Message

    The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2010-04-23T09:16:00). Exception Message: Cannot send mails to mail server. (The operation has timed out.).

    )

    It happened because of longer recipients queue

    Below is the sql mail code

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'SQLNotification',

    --@recipients = @recipients,

    @recipients = 'Bkumar@mymail.com;Bkumar@mymail.com;Bkumar@mymail.com;Bkumar@mymail.com;Bkumar@mymail.com;Bkumar@mymail.com;Bkumar@mymail.com;Bkumar@mymail.com;Bkumar@mymail.com;Bkumar@mymail.com;Bkumar@mymail.com;Bkumar@mymail.com;Bkumar@mymail.com;Bkumar@mymail.com;Bkumar@mymail.com;Bkumar@mymail.com;Bkumar@mymail.com;Bkumar@mymail.com;Bkumar@mymail.com;Bkumar@mymail.com;Bkumar@mymail.com;ved.agarwal@cvent.com',

    @copy_recipients = '',

    @subject = 'BirthDay''s Alert',

    @body = '',

    @body_format = 'TEXT',

    @query = '',

    @attach_query_result_as_file = 0,

    @query_result_header = 0

    One solution: Send the mail by putting it in loop (send one by one to everyone)

    but can we have good solution ?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Hi,

    A few thoughts. Your mail server/gateway may have limitations on the number of recipients as an anti-spam measure. If your mail admin is available I would ask. Can you send the message successfully with another mail client? Can your mail admin create a distribution list on the server instead? Perhaps you can split up the recipients between the recipients and copy recipients parameters.

  • Can i check myself without his interference ?. or can you suggest any other way to send mails to longer list

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Bhuvnesh (4/24/2010)


    Can i check myself without his interference ?. or can you suggest any other way to send mails to longer list

    If you don't have access to the mail administrator you could send email incrementing the number of recipients until you find the limit.

    Other wise you can loop with a cursor such that each email has an acceptable number of recipients. The email admin also might not like this and could reject as an anti-spam measure, two many emails within a certain time period.

  • finally i got fix for this problem instead oif having concatenated email id , i replaced it with group ( active directory email group ) which contains all email id of target list. it worked for me 🙂

    anyways thanks for all who help me here

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 5 posts - 1 through 4 (of 4 total)

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