SQL Server DB_SendMail errors

[poffle]

Howzit Okes,

I need a bit of help. I've googled this and i think i've answered my own question, but just wondering if you guys possibly have a work around or something. If i run this with an attachment larger than set size set i get an error:

Msg 22051, Level 16, State 1, Line 0
File attachment or query results size exceeds allowable value of 1000000 bytes.

Cool, thats fine. The problem is, the Try Catch doesn't catch this error and the proc just carries on as usual doing everything else. Does anyone know how to catch these errors?

It does catch the standard return code errors from sp_send_dbmail.
http://sql-articles.com/blogs/return-code-values-documentation-for-sp_send_dbmail-procedure/

BEGIN TRY
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'S-EMAIL'
,@from_address = @FromAddress
,@recipients = @ToAddress
,@copy_recipients = @CC
,@Blind_copy_recipients = @bcc
,@subject = @Subject
,@body = @Body
,@file_attachments = @Attachment
End Try
Begin Catch
Set @Error = ERROR_MESSAGE()
insert Into MailError(ErrorMessage)
values(@Error)
END Catch

 

[_kabal_]

http://stackoverflow.com/questions/...wed-attachment-size-for-email-sent-using-msdb

the real question is, why exactly are you sending email in sql server.

 

[stevenv]

Sjoe, programming in TSQL. Never a good thing. Unless it's a query.

 

[poffle]

Thanks for the link, but that doesn't solve the issue, because if the try catch is not catching this error, then all the other errors that come up it won't catch.

Ya i know, but this is what my company asked me to do, i'm going to tune them no today and see what i can do in c# (with my basic retarded knowledge of it ).