sql server 2005 发送邮件

最新推荐文章于 2024-10-08 17:35:38 发布

dengenming

最新推荐文章于 2024-10-08 17:35:38 发布

阅读量2k

点赞数

本文链接：https://blog.csdn.net/dengenming/article/details/1356722

版权

方法一
EXEC msdb.dbo.sp_send_dbmail
                  @profile_name = 'eval',
                  @recipients = 'you@emailaddress.com',
                  @body_format = 'HTML',
                  @body = 'Test Body lalalala',
                  @subject = 'Test Subject lalalalala';

方法2

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/*
--=============================================================================================
--== Name   - sp_SMTPMail
--== Purpose - Send an email using an SMTP Mailer COM Component
--== Input Parameters - @SenderName, @SenderAddress, @RecipientName, @RecipientAddress
--==        @Subject, @Body, @MailServer
--==
--==
--== Usage - exec sp_SMTPMail @SenderName='Damian', @SenderAddress='damian@bdussy.com',
--==   @RecipientName = 'Someone', @RecipientAddress = 'someone@someplace.com',
--==   @Subject='SQL Test', @body='Hello, this is a test email from SQL Server'
--==
--== Created Date - Thursday, July 19, 2001
--== Created by  - Damian Maclennan
--==     merkin@sqlteam.com
--==     www.bdussy.com/damian
--==
--== Disclaimer - This procedure has been created for demonstration purposes. It has not
--==    been created for production purposes and I hold no liability for any
--==    unwanted effects of running it.
--=============================================================================================
*/
ALTER Procedure [dbo].[sp_SMTPMail]

@SenderName varchar(100),
@SenderAddress varchar(100),
@RecipientName varchar(100),
@RecipientAddress varchar(100),
@Subject varchar(200),
@Body varchar(8000),
@MailServer varchar(100) = 'localhost'

AS

SET nocount on

declare @oMail int --Object reference
declare @resultcode int

EXEC @resultcode = sp_OACreate 'SMTPsvg.Mailer', @oMail OUT

if @resultcode = 0
BEGIN
  EXEC @resultcode = sp_OASetProperty @oMail, 'RemoteHost', @mailserver
  EXEC @resultcode = sp_OASetProperty @oMail, 'FromName', @SenderName
  EXEC @resultcode = sp_OASetProperty @oMail, 'FromAddress', @SenderAddress

  EXEC @resultcode = sp_OAMethod @oMail, 'AddRecipient', NULL, @RecipientName, @RecipientAddress

  EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject
  EXEC @resultcode = sp_OASetProperty @oMail, 'BodyText', @Body

  EXEC @resultcode = sp_OAMethod @oMail, 'SendMail', NULL

  EXEC sp_OADestroy @oMail
END

SET nocount off

执行:

exec sp_SMTPMail @SenderName='me', @SenderAddress='me@somewhere.com',
@RecipientName = 'Someone', @RecipientAddress = 'someone@someplace.com',
@Subject='SQL Test', @body='Hello, this is a test email from SQL Server'