SQL Server - Email Fails

Dear All

I have created a New SP that is iterating (Cursor) a Project table and extracting the list of Email address, then sending the specific data to all the Project Email address.

This SP is scheduled in one of the Job to run on every Sunday morning while no other process is going on.

While Email are triggering, But not for all projects. What I mean is that out of 10 valid projects, 1 or two triggers and deliver to recipients but for all other fails.

I checked the Log File and observed the Error :
the mail could not be sent to the recipients because of the mail server failure. cannot send mails to mail server. (failure sending mail.).

Please suggest how may I fix the issue,

according to my analysis and doubt, it looks like that due to Looping, the mail server may gets exhausted, resulting in failure. If this is the case, how may I delay the Loop process OR how may I increase the time delay?

regards
LVL 4
musalmanERP ConsultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Barry CunneyCommented:
Hi musalman,
The following is how in T-SQL one does a 5 second wait
WAITFOR DELAY '00:00:05'

Open in new window



Try and do some investigation on the mail server(look for relevant logs) to try and fully understand where the failure is and what exactly the failure is.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ste5anSenior DeveloperCommented:
How many mails are sent per execution?

This seems to be primarily a mail server failure, thus you should take a look at the mail server logs.
0
musalmanERP ConsultantAuthor Commented:
ste5an:
There are 3-7 email addresses per execution, so where ever less, might be getting success!

Barry Cunney:
I will first use the delay methof for 10 - 20 sec, if this works, then nothing to check, If still it misbehaves, I need to look into the logs,

However, I am not sure how to check the logs further in detail ...

Regards
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

LEARN MORE
musalmanERP ConsultantAuthor Commented:
Barry Cunney: :

After adding a Delay of 20 sec, it seems that all the Email status is appearing as Sent in the sysmail_allitems table.

Tomorrow. I will check with the recipients, and will revert back for any comments.

Thanks for the support.
0
ste5anSenior DeveloperCommented:
Sounds like an anti-spam delay on your mail server. Check the configuration, whether you can disable it for you SQL Servers IP.
0
musalmanERP ConsultantAuthor Commented:
Thanks. Time delay worked !
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.