|
|
Posted by Plamen Ratchev on 01/05/08 03:30
In general if I have an option I would prefer to handle e-mail notifications
at the application layer (that is the .NET application for example), where
this is much easier and more natural. In that case you just pass the output
parameters back to the application layer and use the utilities at hand to
send the e-mail notification.
Based on your notes seems that you need to send the e-mail notification from
inside the stored procedure. Here are a couple options:
1). If on SQL Server 2000 then you can use the built-in extended stored
procedure xp_sendmail. An example will be something like this:
EXEC master.dbo.xp_sendmail
@recipients=N'test@company.com',
@message=N'Invalid SSN.'
You can read more about all options and configuration for for xp_sendmail
here:
http://technet.microsoft.com/en-us/library/ms189505.aspx
2). On SQL Server 2005 you can use sp_send_dbmail. Here is an example:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Test',
@recipients = 'test@company.com',
@body = 'Invalid SSN.',
@subject = 'Automated notification'
More about sp_send_dbmail here:
http://msdn2.microsoft.com/en-us/library/ms190307.aspx
3). On SQL Server 2000 (and I have seen posts it works on SQL Server 2005
too) you can use the third party extended stored procedure xp_smtp_sendmail.
It is using directly SMTP (while xp_sendmail uses MAPI), like sp_send_dbmail
does. More info about it here:
http://sqldev.net/xp/xpsmtp.htm
Note that all those methods for sending e-mail are not automatically
available. Read the information at the above links on security and
configuration.
HTH,
Plamen Ratchev
http://www.SQLStudio.com
Navigation:
[Reply to this message]
|