Use SQL server to send an email (posting)

In. net, you know that you can use system. Web. Mail to send emails. In the Framework 1 . 1 supports verification.

Private   Void Page_load ( Object Sender, system. eventargs E)
{
Mailmessage mail =   New Mailmessage ();
Mail. =   " 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 @ objectInt
Declare @ HRInt

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, ' Subject ' , @ Subject

Exec @ HR=Sp_oasetproperty @ object,'Textbody', @ Body
Exec @ HR=Sp_oamethod @ object,'Send', Null

--Error Identification
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.