how to setup DatabaseMail

最新推荐文章于 2025-12-04 16:24:15 发布

原创最新推荐文章于 2025-12-04 16:24:15 发布 · 839 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#database #sql server #email #go #access #ssl

本文介绍如何在SQL Server 2005中通过T-SQL命令配置邮件服务，包括启用高级选项、设置Database Mail账户、创建邮件配置文件，并演示了发送测试邮件的方法。

--SQL Server 2005 Surface Area Configuration
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

-- Creates a new Database Mail account
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'cdq',
@description = 'A mail account of Gmail.',
@email_address = 'cdq@gmail.com',
@replyto_address = 'cdq@gmail.com',
@display_name = 'cdq@gmail.com',
@mailserver_name = 'smtp.gmail.com',
@port = 587,
@username ='cdq@gmail.com',
@password ='19800101',
@enable_ssl = 1

-- Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'MyDBMAIL',
@description = 'Profile used for test.'

-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'MyDBMAIL',
@account_name = 'cdq',
@sequence_number =1 ;

-- Grant access to the profile to all users in the msdb database
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'MyDBMAIL',
@principal_name = 'public',
@is_default = 0 ; -- later, you can update it through T-SQL: 'update sysmail_principalprofile set ...'

--After this configuration you can send mail by calling the system store procedure dbo.sp_send_dbmail
EXECUTE msdb.dbo.sp_send_dbmail
@profile_name = 'MyDBMAIL',
@recipients = 'cdq@gmail.com',
@subject = 'MyDBMAIL',
@body = 'Test Email'

select sent_status,* from dbo.sysmail_allitems