Solved

SQL Server - Email Fails

Posted on 2016-09-04
6
52 Views
Last Modified: 2016-09-18
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
0
Comment
Question by:musalman
  • 3
  • 2
6 Comments
 
LVL 17

Accepted Solution

by:
Barry Cunney earned 500 total points
ID: 41784366
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
 
LVL 33

Expert Comment

by:ste5an
ID: 41784371
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
 
LVL 4

Author Comment

by:musalman
ID: 41785108
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 4

Author Comment

by:musalman
ID: 41785127
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
 
LVL 33

Expert Comment

by:ste5an
ID: 41785170
Sounds like an anti-spam delay on your mail server. Check the configuration, whether you can disable it for you SQL Servers IP.
0
 
LVL 4

Author Closing Comment

by:musalman
ID: 41803652
Thanks. Time delay worked !
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question