New Mail sending methods in SQL Server

This is a new method. It is actually a technology that has been used for a long time!

In. net, you know that you can use system. Web. Mail to send emails. Verification is supported in 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 a method for sending emails under. net. For details, see:

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

 

In SQL Server, we generally use the mail sending method of SQL itself, but it is also cumbersome to configure exchage server, outlook, and so on. Many people complain that the configuration is not successful.

In fact, we can create an OLE object instance in SQL Server and call the sending component provided by IIS SMTP to send emails.

To create this stored procedure, you must modify the name of the smtpserver.

Create procedure sys_sendmail @ from varchar (100), @ to varchar (100), @ BCC varchar (500), @ subject varchar (400) = "", @ 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 verification. If the server needs verification, you must modify the user name 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', @
Exec @ hR = sp_oasetproperty @ object, 'bcc ', @ BCC
Exec @ hR = sp_oasetproperty @ object, 'from', @ from
Exec @ hR = sp_oasetproperty @ object, 'subobject', @ subject

Exec @ hR = sp_oasetproperty @ object, 'textbody', @ body
Exec @ hR = sp_oamethod @ object, 'send', null

-- Identify errors
If @ HR <> 0
Begin
Exec sp_oageterrorinfo @ object
Return @ object
End
Print 'success'
Exec @ hR = sp_oadestroy @ object

Go

Note: Make sure that you install SMTP to access the CDO object.