>>>In foxpro we have an application that checks periodically for new records on a table (dbf) and when it founds one it will send and e-mail based on the new record. The system was conceived originally for pagers so it has some limitations, but it was later been used for some other notifications (regular e-mails) so we were thinking on writing a .NET service to do the same task with a SQL back-end and more options, but then we realized that the service is completely superfluous as we can just write an instead of insert trigger to handle the e-mail, so we wrote said trigger and it is working fine.
>>>
>>>The question is if there is any ill-effect of doing something like this, for example, would SQL server have any problems handling something like 500 e-mails per day? The emails might come in waves, meaning that it might need to send lots of e-mails in a short period and then just idle. Is there any known issue with sp_send_dbmail that I should be aware of? I tried to google for it but lot of worthless reading.
>>>
>>>I do not expect this to be a problem for the server as it does not seem to be a lot of work, but I thought I would ask before proposing this solution.
>>>
>>>Thanks,
>>>Hugo
>>
>>You need to create a job in SQL Server that will send e-mails. The trigger will only insert record into the e-mails table. With this approach you should not have any problems, but don't put this procedure inside the trigger itself.
>
>There is actually a SQL Server SP that will send emails directly. I just did this on a recently completed assignment. The interface is simple -- just pass it the sender, recipient(s), subject, and body. The body can contain HTML if you want to get fancy with the appearance. I think the DBA had to do a bit of setup but not much.
>
>We weren't stressing the engine, sending a couple hundred emails a day, so I can't address any performance issues.

sp_send_dbMail - the SP Hugo is using. In any case, the call to this SP must be in a job, not in a trigger - that was my point.