My set up is: SQL Server 2008 R1 32bit.
What i am trying to do is set up a data driven subscription service that will automatically email approx 2000 students an individualised copy of their exam timetable (I work at a college). (I'm actually trying to use Data driven subscriptions to send out lots of different types of reports automatically to email addresses, these include confirmation of enrollment reports, timetable reports and a whole manner of different reports to our students. We have around 5000 students in total ... So i guess that would be the maximum number that we would be emailing.
Data driven subscription services seems to do this extremely well, except after about the first 100 emails it sends it stops sending and all i get is the error from the SMTP server
"The message could not be sent to the SMTP server. The transport error code was 0x800ccc67. The server response was 421 4.3.2 The maximum number of concurrent connections has exceeded a limit, closing transmission channel".
Unfortunately the SMTP server is out of my control, and I've had a look around on topics and they all suggest that the maximum number of concurrent connections should be set higher to the max number of recipients ..... however this isn't really an option as I don't have control over this server and settings.
My question is this, is there anything that can be set in SSRS to limit the number of concurrent connections its making to the email server? Can it be set to send just a few emails at a time?? It doesn't matter that this would take a while to get through them all, it would be fine if it took hours or even days to send as at the moment its just falling over because its trying to send all at once.
Any help will be very much appreciated