- 首页 > 资讯 > SQL Server > 正文
如何建立SQL Server数据库邮件
如何建立SQL Server数据库邮件
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
- 上一篇：mysql查询优化-Show命令
- 下一篇：九度 1011 最大连续子序列
相关文章
图文推荐
- 文章
- 推荐
- 热门新闻