New ways to send messages in SQL Server

server| Send mail
Say is a new way, in fact, is already used in the technology, so put it up!

In. NET, you know that you can use System.Web.Mail to send messages. Validation is supported under Framework 1.1.

private void Page_Load (object sender, System.EventArgs e)
{
MailMessage mail = new MailMessage ();
Mail. to = "me@mycompany.com";
Mail. from = "you@yourcompany.com";
Mail. Subject = "This is a test email.";
Mail. BODY = "Some text goes Here";
Mail. Fields.Add ("Http://schemas.microsoft.com/cdo/configuration/smtpauthenticate", "1"); Basic Authentication
Mail. Fields.Add ("Http://schemas.microsoft.com/cdo/configuration/sendusername", "My_username_here"); Set your username here
Mail. Fields.Add ("Http://schemas.microsoft.com/cdo/configuration/sendpassword", "Super_secret"); Set your password here

Smtpmail.smtpserver = "mail.mycompany.com"; Your real server goes here
Smtpmail.send (mail);
}

I used to write about. Net send mail under the method, see:

Http://dev.csdn.net/develop/article/17/17189.shtm



In SQL Server, we generally use the way the mail is sent by SQL itself, but it is also cumbersome to configure Exchage Server, Outlook, and so on. Many people complain that configuration is not successful.

In fact, we can create an OLE object instance in SQL Server and invoke the sending component of IIS SMTP to send mail.

We build this stored procedure, you need to modify the place that is, SmtpServer's name

Create PROCEDURE sys_sendmail @From varchar (m), @To varchar (m), @Bcc varchar (+), @Subject varchar () = "", @Body ntext = ""

As

Declare @object int
Declare @hr int

EXEC @hr = sp_OACreate ' CDO. Message ', @object out

EXEC @hr = sp_OASetProperty @object, ' Configuration.fields ("http://schemas.microsoft.com/cdo/configuration/sendusing"). Value ', ' 2 '
EXEC @hr = sp_OASetProperty @object, ' Configuration.fields ("Http://schemas.microsoft.com/cdo/configuration/smtpserver"). Value ', ' smtp.163.com '

--The following three statements are SMTP authentication and if the server needs to be validated, you need to change the username and password
EXEC @hr = sp_OASetProperty @object, ' Configuration.fields ("http://schemas.microsoft.com/cdo/configuration/ Smtpauthenticate "). Value ', ' 1 '
EXEC @hr = sp_OASetProperty @object, ' Configuration.fields ("Http://schemas.microsoft.com/cdo/configuration/sendusername"). Value ', ' lihonggen0 '
EXEC @hr = sp_OASetProperty @object, ' Configuration.fields ("Http://schemas.microsoft.com/cdo/configuration/sendpassword"). Value ', ' xxx '

EXEC @hr = sp_OAMethod @object, ' Configuration.Fields.Update ', null
EXEC @hr = sp_OASetProperty @object, ' to ', @To
EXEC @hr = sp_OASetProperty @object, ' Bcc ', @Bcc
EXEC @hr = sp_OASetProperty @object, ' from ', @From
EXEC @hr = sp_OASetProperty @object, ' Subject ', @Subject

EXEC @hr = sp_OASetProperty @object, ' TextBody ', @Body
EXEC @hr = sp_OAMethod @object, ' Send ', NULL

--Error judgment
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object
Return @object
End
PRINT ' Success '
EXEC @hr = sp_OADestroy @object

Go

Note: You must be sure to install SMTP to access the CDO object.