New ways to send messages in SQL Server

Source: Internet
Author: User
Tags mail mailmessage net send
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.


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.