From: http://blog.9cbs.net/lihonggen0/Archive/2004/09/14/103490.aspx
In SQL Server, we generally use the SQL itself's email sending mode, but you need to configure Exchage Server, Outlook, etc., which is also a cumbersome thing. Many people complain that the configuration is unsuccessful.
In fact, we can create an OLE object instance in SQL Server, call the IIS SMTP's own transmitting components to implement mail delivery.
We build this stored procedure, where you need to modify, the name of 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 to be verified, you must have three sentences, you need to modify 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', nullEXEC @hr = sp_OASetProperty @object, 'To', @ToEXEC @hr = sp_OASetProperty @object, 'Bcc', @BccEXEC @hr = sp_OASetProperty @object, 'From', @FromEXEC @hr = sp_OASetProperty @object, 'Subject', @SubjectEXEC @hr = sp_OASetProperty @object, 'TextBody', @BodyEXEC @hr = sp_OAMethod @object, 'Send', NULL
- Judging error IF @hr <> 0begin exec sp_oageterrorinfo @object return @objectndprint 'success'exec @hr = sp_oadestroy @Object
Go
Note: You must ensure that SMTP is installed, you can access the CDO object.
