Re: Error handling in stored procedure AND checking

On Jan 4, 10:30 pm, "Plamen Ratchev" <Pla...@xxxxxxxxxxxxx> wrote:
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't...@xxxxxxxxxxx',
       @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 = 't...@xxxxxxxxxxx',
       @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 Ratchevhttp://www.SQLStudio.com

Thanks for the help, I greatly appreciate it. I will look into the
link provided.
.

Relevant Pages

  • Re: Error handling in stored procedure AND checking
    ... In general if I have an option I would prefer to handle e-mail notifications at the application layer, 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. ...
    (comp.databases.ms-sqlserver)
  • Re: DataSet Question -- Simple Concept? Beginner to 3-Tier asp.net
    ... I am trying to be a thorough coder and create an app in layers. ... If I use VS2005 to create a typed data set, I see that I can allow VS ... create a stored procedure ReturnProductsOverOneDollar. ... I'm using stored procedures in my Data Access Layer to write the info ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: DataSet Question -- Simple Concept? Beginner to 3-Tier asp.net
    ... information back and forth to that layer and let it do all the work. ... datasets with tableadapters that are now "disconnected" but most decidedly ... I am trying to be a thorough coder and create an app in layers. ... create a stored procedure ReturnProductsOverOneDollar. ...
    (microsoft.public.dotnet.framework.adonet)
  • Architecture: Appropriate location for SP name and parameter information
    ... store stored procedure names and parameter information such as ... Business Logic layer -- receives values from layer 1. ... knows the SP name, parameter names, and parameter types. ... as well as the parameter information in some ...
    (microsoft.public.dotnet.framework.adonet)
  • Add to stored procedure or bind in code?
    ... I have an info layer, with private variables, constructors and properties, a data access layer and a business layer. ... I am populating a part of the form using a join from a stored procedure. ... I've changed my Permit layer info to reflect the individual ID. ... Sub LoadDivisionbyDivisionID() ...
    (microsoft.public.dotnet.framework.windowsforms.databinding)