如何建立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