如何建立SQL Server数据库邮件
来源:互联网 发布:蛋疼到底有多疼 知乎 编辑:程序博客网 时间:2024/06/06 05:05
USE msdb
GO
DECLARE @ProfileName VARCHAR(255)
DECLARE @AccountName VARCHAR(255)
DECLARE @SMTPAddress VARCHAR(255)
DECLARE @EmailAddress VARCHAR(128)
DECLARE @DisplayUser VARCHAR(128)
SET @ProfileName = 'DBMailProfile';
SET @AccountName = 'DBMailAccount';
SET @SMTPAddress = 'mail.yoursmtpserver.com';
SET @EmailAddress = 'DBMail@yoursmtpserver.com';
SET @DisplayUser = 'The Mail Man';
-- Deleting Profile Account, if exists Profile Account
IF EXISTS ( SELECT 1
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
EXECUTE sysmail_delete_profileaccount_sp @profile_name = @ProfileName,
@account_name = @AccountName
END
-- Deleting Profile, if exists Profile
IF EXISTS ( SELECT 1
FROM msdb.dbo.sysmail_profile p
WHERE p.name = @ProfileName )
BEGIN
EXECUTE sysmail_delete_profile_sp @profile_name = @ProfileName
END
-- Deleting Account, if exists Account
IF EXISTS ( SELECT 1
FROM msdb.dbo.sysmail_account a
WHERE a.name = @AccountName )
BEGIN
EXECUTE sysmail_delete_account_sp @account_name = @AccountName
END
-- Create Account
EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = @AccountName,
@email_address = @EmailAddress, @display_name = @DisplayUser,
@mailserver_name = @SMTPAddress
-- Create Profile
EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = @ProfileName
-- Create Profile Account
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = @ProfileName,
@account_name = @AccountName, @sequence_number = 1;
--Turn On Database Mail XPs, if the configuration is turn off
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO
-- Test send email
EXEC msdb.dbo.sp_send_dbmail @recipients = 'test@gmail.cn',
@body = 'Test Email Body', @subject = 'Test Email Subject',
@profile_name = 'DBMailProfile'
-- Search the result that send email
SELECT * FROM msdb.dbo.sysmail_allitems
GO
DECLARE @ProfileName VARCHAR(255)
DECLARE @AccountName VARCHAR(255)
DECLARE @SMTPAddress VARCHAR(255)
DECLARE @EmailAddress VARCHAR(128)
DECLARE @DisplayUser VARCHAR(128)
SET @ProfileName = 'DBMailProfile';
SET @AccountName = 'DBMailAccount';
SET @SMTPAddress = 'mail.yoursmtpserver.com';
SET @EmailAddress = 'DBMail@yoursmtpserver.com';
SET @DisplayUser = 'The Mail Man';
-- Deleting Profile Account, if exists Profile Account
IF EXISTS ( SELECT 1
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
EXECUTE sysmail_delete_profileaccount_sp @profile_name = @ProfileName,
@account_name = @AccountName
END
-- Deleting Profile, if exists Profile
IF EXISTS ( SELECT 1
FROM msdb.dbo.sysmail_profile p
WHERE p.name = @ProfileName )
BEGIN
EXECUTE sysmail_delete_profile_sp @profile_name = @ProfileName
END
-- Deleting Account, if exists Account
IF EXISTS ( SELECT 1
FROM msdb.dbo.sysmail_account a
WHERE a.name = @AccountName )
BEGIN
EXECUTE sysmail_delete_account_sp @account_name = @AccountName
END
-- Create Account
EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = @AccountName,
@email_address = @EmailAddress, @display_name = @DisplayUser,
@mailserver_name = @SMTPAddress
-- Create Profile
EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = @ProfileName
-- Create Profile Account
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = @ProfileName,
@account_name = @AccountName, @sequence_number = 1;
--Turn On Database Mail XPs, if the configuration is turn off
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO
-- Test send email
EXEC msdb.dbo.sp_send_dbmail @recipients = 'test@gmail.cn',
@body = 'Test Email Body', @subject = 'Test Email Subject',
@profile_name = 'DBMailProfile'
-- Search the result that send email
SELECT * FROM msdb.dbo.sysmail_allitems
- 如何建立SQL Server 2005数据库邮件
- 如何建立SQL Server数据库邮件
- sql server 2000 中如何建立图形数据库??急用
- Sql Server 配置数据库邮件
- Sql Server 配置数据库邮件
- sql Server 数据库发送邮件
- SQL SERVER创建数据库邮件
- SQL Server 创建数据库邮件
- SQL Server建立简单数据库
- SQL Server 2008R2建立的数据库如何导入SQL Server 2000
- 配置SQL Server 2005数据库邮件
- SQL Server 2005 数据库邮件 使用要点
- SQL Server 配置数据库邮件功能
- SQL Server 2005/2008数据库邮件
- SQL Server 2008 数据库邮件的配置
- 配置SQL Server 2012数据库邮件
- SQL Server 使用数据库发送邮件(sp_send_dbmail)
- SQL SERVER 数据库邮件发送异常
- mongodb 查看工具rockmongo 安装使用说明
- Terminating app due to uncaught exception 'NSInvalidUnarchiveOperationException', reason: 'Could not
- C#之后台设置html元素的属性
- margin-top在firefox中会绑架父节点的margin的分析
- 一个建库和建表以及插入数据的实例
- 如何建立SQL Server数据库邮件
- ACE中UDP通信
- win7中VS2010中安装CSS3.0问题解决方法
- 生命在于学习!数据结构
- 【转】 杭电ACM题目分类
- 假如生活欺骗了你
- TCP/IP,http,socket,长连接,短连接
- 让kingdee bos不在受license限制
- 使用DevExpress 控件开发通用查询控件(Winform)