存档
‘Scripts’ 分类的存档
08/06 06
没有评论
225 次查看
/*************************************************************
All about SQL Server 2005 database mail configuration
Reference:
http://technet.microsoft.com/zh-cn/library/ms177580.aspx
*************************************************************/
SP_CONFIGURE 'show advanced options', 1 ;
GO
RECONFIGURE ;
GO
SP_CONFIGURE 'Database Mail XPs', 1 ;
GO
RECONFIGURE ;
GO
/**************************************************
Config database mail
**************************************************/
-- Creates a new Database Mail account
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'SQL Server Monitoring',
@description = 'SQL Server Monitoring',
@email_address = 'sql-monitoring@your-company.com',
@replyto_address = 'sql-monitoring@your-company.com',
@display_name = 'SQL Server Monitoring',
@mailserver_name = 'smtp.your-company.com',
@port = 25,
@enable_ssl = 0 ;
-- Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'YourCompanyMailProfile',
@description = 'Profile used for Your Company SQL Servers.';
-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'YourCompanyMailProfile',
@account_name = 'SQL Server Monitoring',
@sequence_number =1 ;
-- Grant access to the profile to all users in the msdb database
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@principal_name = 'public',
@profile_name = 'YourCompanyMailProfile',
@is_default = 1 ; -- later, you can update it through T-SQL: 'update sysmail_principalprofile set ...'
/**************************************************
Send mail and query all mail items
**************************************************/
-- 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 = 'YourCompanyMailProfile',
@recipients = 'MyEmail@your-company.com;',
@subject = 'Test Database Mail',
@body = 'Test e-mail body text';
-- Check send mail status
select sent_status,* from msdb.dbo.sysmail_allitems
/**************************************************
Remove all database mail settings
**************************************************/
-- Delete principal
EXECUTE msdb.dbo.sysmail_delete_principalprofile_sp
@principal_name = 'public';
-- Delete profile
EXECUTE msdb.dbo.sysmail_delete_profile_sp
@profile_name = 'YourCompanyMailProfile';
-- Delete account
EXECUTE msdb.dbo.sysmail_delete_account_sp
@account_name = 'SQL Server Monitoring' ;
GO
SP_CONFIGURE 'Database Mail XPs', 0 ;
GO
RECONFIGURE ;
GO
All about SQL Server 2005 database mail configuration
Reference:
http://technet.microsoft.com/zh-cn/library/ms177580.aspx
*************************************************************/
SP_CONFIGURE 'show advanced options', 1 ;
GO
RECONFIGURE ;
GO
SP_CONFIGURE 'Database Mail XPs', 1 ;
GO
RECONFIGURE ;
GO
/**************************************************
Config database mail
**************************************************/
-- Creates a new Database Mail account
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'SQL Server Monitoring',
@description = 'SQL Server Monitoring',
@email_address = 'sql-monitoring@your-company.com',
@replyto_address = 'sql-monitoring@your-company.com',
@display_name = 'SQL Server Monitoring',
@mailserver_name = 'smtp.your-company.com',
@port = 25,
@enable_ssl = 0 ;
-- Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'YourCompanyMailProfile',
@description = 'Profile used for Your Company SQL Servers.';
-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'YourCompanyMailProfile',
@account_name = 'SQL Server Monitoring',
@sequence_number =1 ;
-- Grant access to the profile to all users in the msdb database
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@principal_name = 'public',
@profile_name = 'YourCompanyMailProfile',
@is_default = 1 ; -- later, you can update it through T-SQL: 'update sysmail_principalprofile set ...'
/**************************************************
Send mail and query all mail items
**************************************************/
-- 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 = 'YourCompanyMailProfile',
@recipients = 'MyEmail@your-company.com;',
@subject = 'Test Database Mail',
@body = 'Test e-mail body text';
-- Check send mail status
select sent_status,* from msdb.dbo.sysmail_allitems
/**************************************************
Remove all database mail settings
**************************************************/
-- Delete principal
EXECUTE msdb.dbo.sysmail_delete_principalprofile_sp
@principal_name = 'public';
-- Delete profile
EXECUTE msdb.dbo.sysmail_delete_profile_sp
@profile_name = 'YourCompanyMailProfile';
-- Delete account
EXECUTE msdb.dbo.sysmail_delete_account_sp
@account_name = 'SQL Server Monitoring' ;
GO
SP_CONFIGURE 'Database Mail XPs', 0 ;
GO
RECONFIGURE ;
GO
本文采用知识共享署名-非商业性使用-相同方式共享 3.0 Unported许可协议发布，转载请保留此信息
作者：马齿苋 | 链接：http://www.dbabeta.com/2008/sql2005_dbmail_configuration.html
作者：马齿苋 | 链接：http://www.dbabeta.com/2008/sql2005_dbmail_configuration.html
近期评论