sql server 发送邮件

 

 

 

 

sql server 发送邮件

     declare 
            @html nvarchar(MAX),
            @subject nvarchar(MAX)='your subject',
            @ErrorNO int  

           select @ErrorNO= ERROR_NUMBER()        
    
             begin
                        select @html= '<p>your message Insert Error. <br/>Error Number: </p>' + convert(nvarchar,@ErrorNO)+'<br/>ErrorMessage:'+ERROR_MESSAGE()
                        exec    msdb.dbo.sp_send_dbmail
                                    @PROFILE_NAME   = 'SQL_Profile'
                                ,   @RECIPIENTS = 'abc@abc.com'  
                                ,   @SUBJECT    = @subject
                                ,   @BODY       = @html
                                ,   @BODY_format  = 'HTML'
                                ,@Importance='High'
             end

 

 

以下为sql server提供的邮件模板

-------------------------------------------------------------
--  Database Mail Simple Configuration Template.
--
--  This template creates a Database Mail profile, an SMTP account and 
--  associates the account to the profile.
--  The template does not grant access to the new profile for
--  any database principals.  Use msdb.dbo.sysmail_add_principalprofile
--  to grant access to the new profile for users who are not
--  members of sysadmin.
-------------------------------------------------------------

DECLARE @profile_name sysname,
        @account_name sysname,
        @SMTP_servername sysname,
        @email_address NVARCHAR(128),
        @display_name NVARCHAR(128);

-- Profile name. Replace with the name for your profile
        SET @profile_name = '<profile_name,sysname,SampleProfile>';

-- Account information. Replace with the information for your account.

        SET @account_name = '<account_name,sysname,SampleAccount>';
        SET @SMTP_servername = '<SMTP_servername,sysname,your SMTP server name>';
        SET @email_address = '<email_address,nvarchar(128),sender email address>';
        SET @display_name = '<display_name,nvarchar(128),name of the sender>';


-- Verify the specified account and profile do not already exist.
IF EXISTS (SELECT * FROM msdb.dbo.sysmail_profile WHERE name = @profile_name)
BEGIN
  RAISERROR('The specified Database Mail profile (<profile_name,sysname,SampleProfile>) already exists.', 16, 1);
  GOTO done;
END;

IF EXISTS (SELECT * FROM msdb.dbo.sysmail_account WHERE name = @account_name )
BEGIN
 RAISERROR('The specified Database Mail account (<account_name,sysname,SampleAccount>) already exists.', 16, 1) ;
 GOTO done;
END;

-- Start a transaction before adding the account and the profile
BEGIN TRANSACTION ;

DECLARE @rv INT;

-- Add the account
EXECUTE @rv=msdb.dbo.sysmail_add_account_sp
    @account_name = @account_name,
    @email_address = @email_address,
    @display_name = @display_name,
    @mailserver_name = @SMTP_servername;

IF @rv<>0
BEGIN
    RAISERROR('Failed to create the specified Database Mail account (<account_name,sysname,SampleAccount>).', 16, 1) ;
    GOTO done;
END

-- Add the profile
EXECUTE @rv=msdb.dbo.sysmail_add_profile_sp
    @profile_name = @profile_name ;

IF @rv<>0
BEGIN
    RAISERROR('Failed to create the specified Database Mail profile (<profile_name,sysname,SampleProfile>).', 16, 1);
    ROLLBACK TRANSACTION;
    GOTO done;
END;

-- Associate the account with the profile.
EXECUTE @rv=msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = @profile_name,
    @account_name = @account_name,
    @sequence_number = 1 ;

IF @rv<>0
BEGIN
    RAISERROR('Failed to associate the specified profile with the specified account (<account_name,sysname,SampleAccount>).', 16, 1) ;
    ROLLBACK TRANSACTION;
    GOTO done;
END;

COMMIT TRANSACTION;

done:

GO

 

 

以下为sql template打开方法