SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO USE Master GO if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[send_mail]'') and OBJECTPROPERTY(id, N''IsProcedure'') = 1) drop procedure [dbo].[send_mail] GO CREATE PROCEDURE [dbo].[send_mail] @From varchar(1000) , --发件人 @To varchar(1000) , --收件人 @Subject nvarchar(128)='''', --标题 @Body nvarchar(4000) ='''' --正文 with encryption/*********************************************************************This stored procedure takes the parameters and sends an e-mail. All the mail configurations are hard-coded in the stored procedure. Comments are added to the stored procedure where necessary. References to the CDOSYS objects are at the following MSDN Web site: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp***********************************************************************/ AS Declare @iMsg int Declare @hr int Declare @source varchar(255) Declare @description varchar(500) Declare @output varchar(1000) ------ declare @smtpserver varchar(200),@sendusername varchar(200) ,@sendpassword varchar(200) --please set the values before excute the stored procedure--@@@@@@@@ set @smtpserver = ''192.168.1.100'' --smtp服务器 set @sendusername=''master'' --发送认证:用户名 set @sendpassword=''password'' --发送认证:密码if @sendusername='''' or @sendpassword='''' begin raiserror 50000 ''please set the @sendusername and @sendpassword values before excute the stored procedure'' return -1 end--replace the quotation s set @Subject=replace(@Subject,'''''''','''''''''''') set @Body=replace(@Body,'''''''','''''''''''')--************* Create the CDO.Message Object ************************ EXEC @hr = sp_OACreate ''CDO.Message'', @iMsg OUT--***************Configuring the Message Object ****************** -- This is to configure a remote SMTP server. -- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp EXEC @hr = sp_OASetProperty @iMsg, ''Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value'',''2'' -- This is to configure the Server Name or IP address. -- Replace MailServerName by the name or IP of your SMTP Server. EXEC @hr = sp_OASetProperty @iMsg, ''Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value'', @smtpserver -- EXEC @hr = sp_OASetProperty @iMsg, ''Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").Value'',''1'' EXEC @hr = sp_OASetProperty @iMsg, ''Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").Value'', @sendusername EXEC @hr = sp_OASetProperty @iMsg, ''Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").Value'', @sendpassword
-- Save the configurations to the message object. EXEC @hr = sp_OAMethod @iMsg, ''Configuration.Fields.Update'', null-- Set the e-mail parameters. EXEC @hr = sp_OASetProperty @iMsg, ''To'', @To EXEC @hr = sp_OASetProperty @iMsg, ''From'', @From EXEC @hr = sp_OASetProperty @iMsg, ''Subject'', @Subject-- If you are using HTML e-mail, use ''HTMLBody'' instead of ''TextBody''. EXEC @hr = sp_OASetProperty @iMsg, ''TextBody'', @Body EXEC @hr = sp_OAMethod @iMsg, ''Send'', NULL if @@error<>0 or @hr<>0 begin raiserror 55000 ''<send_mail> Error: send mail failed.'' end else begin print ''Success: send mail ok.'' end EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT IF @hr = 0 BEGIN SELECT @output = ''<send_mail> Error Source: '' + @source PRINT @output SELECT @output = ''<send_mail> Error Description: '' + @description PRINT @output END ELSE BEGIN PRINT '' sp_OAGetErrorInfo failed.'' RETURN END-- Do some error handling after each step if you have to. -- Clean up the objects created. EXEC @hr = sp_OADestroy @iMsg ------- GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
GO
SET ANSI_NULLS ON
GO
USE Master
GO
if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[send_mail]'') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)
drop procedure [dbo].[send_mail]
GO
CREATE PROCEDURE [dbo].[send_mail]
@From varchar(1000) , --发件人
@To varchar(1000) , --收件人
@Subject nvarchar(128)='''', --标题
@Body nvarchar(4000) ='''' --正文
with encryption/*********************************************************************This stored procedure takes the parameters and sends an e-mail.
All the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
References to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp***********************************************************************/
AS
Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)
------
declare @smtpserver varchar(200),@sendusername varchar(200) ,@sendpassword varchar(200)
--please set the values before excute the stored procedure--@@@@@@@@
set @smtpserver = ''192.168.1.100'' --smtp服务器
set @sendusername=''master'' --发送认证:用户名
set @sendpassword=''password'' --发送认证:密码if @sendusername='''' or @sendpassword=''''
begin
raiserror 50000 ''please set the @sendusername and @sendpassword values before excute the stored procedure''
return -1
end--replace the quotation s
set @Subject=replace(@Subject,'''''''','''''''''''')
set @Body=replace(@Body,'''''''','''''''''''')--************* Create the CDO.Message Object ************************
EXEC @hr = sp_OACreate ''CDO.Message'', @iMsg OUT--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg, ''Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value'',''2''
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, ''Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value'', @smtpserver
--
EXEC @hr = sp_OASetProperty @iMsg, ''Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").Value'',''1'' EXEC @hr = sp_OASetProperty @iMsg, ''Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").Value'', @sendusername
EXEC @hr = sp_OASetProperty @iMsg, ''Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").Value'', @sendpassword
-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, ''Configuration.Fields.Update'', null-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, ''To'', @To
EXEC @hr = sp_OASetProperty @iMsg, ''From'', @From
EXEC @hr = sp_OASetProperty @iMsg, ''Subject'', @Subject-- If you are using HTML e-mail, use ''HTMLBody'' instead of ''TextBody''.
EXEC @hr = sp_OASetProperty @iMsg, ''TextBody'', @Body
EXEC @hr = sp_OAMethod @iMsg, ''Send'', NULL
if @@error<>0 or @hr<>0
begin
raiserror 55000 ''<send_mail> Error: send mail failed.''
end
else
begin
print ''Success: send mail ok.''
end EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ''<send_mail> Error Source: '' + @source
PRINT @output
SELECT @output = ''<send_mail> Error Description: '' + @description
PRINT @output
END
ELSE
BEGIN
PRINT '' sp_OAGetErrorInfo failed.''
RETURN
END-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
-------
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
http://blog.csdn.net/hyit2004/archive/2007/06/11/1647431.aspx 这个我试了一下,要配置outlook 或者 exchange啊,可是这里没有说明怎么配置的