November 8, 2013 at 8:37 am
I posted the following in Stack Overflow and didn't get any response. I'm just looking for a better way to handle email attachments that are too large without generating an error and then reacting to the error.
When sending emails from SQL on SQL Server 2008 we were getting an error on occasion when the attachments were too large. I was able to increase the maximum attachment size in SQL Server and expect that will pretty much solve the problem however, for prudence sake, I would like to try to trap this error in case it comes up in the future.
I am currently using the return code, which appears to be simply 1 when the attachment is too large, to resend the email sans attachments and provide instead a link to the files on the file system. This part works out just fine.
The problem is that without trapping the error the job that drives this fails, shows as failed in the job history, sends an email to the administrator saying the job failed, and general chaos ensues. Ok, maybe not that bad, but I've read that the try-catch process doesn't work to catch the sp_send_dbmail errors and, being a fan of empirical evidence, proved this for myself. Has anyone found a way to trap this error so the job doesn't show as failed calling undue attention to itself when everything actually ran as expected?
November 8, 2013 at 9:21 am
If all you care about is trapping the error and not sending the email, you probably want to look at using a TRY...CATCH block. http://technet.microsoft.com/en-us/library/ms179296%28v=sql.100%29.aspx
I'm wondering why you're sending huge emails from the database to begin with. Wouldn't you be better off sending a link to the file on the network somewhere instead of sending large attachments?
Oh, and keep an eye on the size of your msdb database. That's where database mail items are stored.
HTH
November 11, 2013 at 7:32 am
As stated in the OP the TRY...CATCH doesn't work with sp_send_dbmail.
This is essentially an email to ticket system and I don't have control over what attachments are sent in from the users. I've increased the maximum size of the attachments to 10MB so I expect that to take care of it for the most part.
If a larger attachment does make it through then the error will be generated and my code will compensate by sending a link instead of an attachment but the logs will still show an error which is what I'm trying to prevent.
I don't want to convert all of the attachments to links because all of the recipients may not be on the local LAN and won't have access to the files.
-pat
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply