如何建立SQL Server 2005数据库邮件

 

列表A中的脚本完成一些清理工作，以便假如我再次运行脚本，就不必担心出现错误。

IF EXISTS


(


SELECT * FROM msdb.dbo.sysmail_profileaccount pa
      JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
      JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id


WHERE
      p.name = @ProfileName AND
      a.name = @AccountName
)


BEGIN
      PRINT 'Deleting Profile Account'
      EXECUTE sysmail_delete_profileaccount_sp
      @profile_name = @ProfileName,
      @account_name = @AccountName


END





IF EXISTS


(


SELECT * FROM msdb.dbo.sysmail_profile p 


WHERE p.name = @ProfileName


)


BEGIN
      PRINT 'Deleting Profile.'
      EXECUTE sysmail_delete_profile_sp
      @profile_name = @ProfileName


END





IF EXISTS


(


SELECT * FROM msdb.dbo.sysmail_account a


WHERE a.name = @AccountName


)


BEGIN
      PRINT 'Deleting Account.'
      EXECUTE sysmail_delete_account_sp
      @account_name = @AccountName


END

下面的脚本给系统增加账户（Account）、用户资料（Profile）和账户-用户资料（Account-Profile）关系。

EXECUTE msdb.dbo.sysmail_add_account_sp

@account_name = @AccountName,

@email_address = @EmailAddress,

@display_name = @DisplayUser,

@mailserver_name = @SMTPAddress

EXECUTE msdb.dbo.sysmail_add_profile_sp

@profile_name = @ProfileName

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

@profile_name = @ProfileName,

@account_name = @AccountName,

@sequence_number = 1 ;

现在一切都已经预备妥当，我将发送一封测试电子邮件。

EXEC msdb.dbo.sp_send_dbmail

@recipients=N'chapman.tim@gmail.com',

@body= 'Test Email Body',

@subject = 'Test Email Subject',

@profile_name = @ProfileName

要检查消息是否发送成功，我可以对sysmail_allitems系统视图执行一次查询。

SELECT * FROM sysmail_allitems

后续讨论

在下一篇文章中，我将为你介绍在SQL Server 2005中使用Database Mail发送电子邮件的各种选项。

共2页: 上一页 [1] 2 下一页

评论加载中…