SQL Server邮件服务 sp_send_dbmail

1：配置数据库邮件

2:配置好之后就可以发送邮件了

发送邮件是以msdb下面是存储过程sp_send_dbmail来发送的

use msdb
Go
DECLARE @tableHTML NVARCHAR(MAX) ;
DECLARE @title NVARCHAR(MAX) ;
@title='测试邮件';
SET @tableHTML =N'标题'
                +N''
					                +N''
					                +N''
					                +N''
					                +N''
					                +N''
					                +CAST ( 
				
					                    (SELECT 
				
					                    td = account,
				
					                    '',td = name,
				
					                    '',td = usd,
				
					                    '',td = paypal,
				
					                     '',td = datetime from Databases.dbo.xxx
				
					
				
					FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX) ) +N' 
				
				account
				
				name 
				
				usd
				
				paypal
				
				datetime
				
				
				
				
				
				
				
				
				
				' ;
                +N'
                +N'
                +N'
                +N'
                +N'
                +CAST (
                    (SELECT
                    td = account,
                    '',td = name,
                    '',td = usd,
                    '',td = paypal,
                     '',td = datetime from Databases.dbo.xxx

FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX) ) +N'
    EXEC msdb.dbo.sp_send_dbmail
            @profile_name='mail config(这里是上面地址配置的邮件配置文件名)',
            @recipients='xxx.163.com;[e-mail]',（收件箱地址）
            @copy_recipients='[e-mail]',（抄送地址）
            @subject = @title,(邮件标题)
            @body = @tableHTML,（正文）
            @body_format = 'HTML' ;

对于上面的格式，注意width="1541px 来设置表格的宽度，否则有些数据会自动换行

F5运行上面是命令就可以发送邮件了

-------------------------------------------------------------------------------------------------------------------------------------------------------
发现写成sp比较方便实用

CREATE PROCEDURE sp_email
        @smail varchar(200),
        @cmail varchar(200)
as
DECLARE @tableHTML NVARCHAR(MAX) ;
declare @title varchar(100)
declare @edate varchar(10)
declare @sum varchar(10)
-- 定义日期和退款金额
select @edate=dd,@sum=ff from openquery(GAME_ADMIN,
'SELECT DATE_ADD(CURDATE(),INTERVAL -1 DAY) dd,ABS(SUM(g_amount)) ff FROM gp_admin.gm_payment_reversed WHERE g_created_date BETWEEN DATE_ADD(CURDATE(),INTERVAL -1 DAY) AND CURDATE()')
-- 定义邮件标题
set @title=@edate+'退款'+@sum
-- 定义邮件内容
set @tableHTML=N'退款订单'
                +N''
					                +N''
					                +N''
					                +N''
					                +N''
					                +N''
					                +N''
					                +N''
					                +N''
					                +N''
					                +CAST ( 
				
					                    ( select td = g_payment_gateway_no,
				
					        '',td = g_created_by,
				
					        '',td = g_payment_type,
				
					        '',td = amount,
				
					        '',td = g_trans_no,
				
					        '',td = g_parent_trans_no,
				
					        '',td = g_trans_error,
				
					        '',td = convert(varchar(19),g_created_date,121),
				
					        '',td = g_merchant_trans_no from openquery(GAME321_ADMIN,'SELECT g_payment_gateway_no,
 
				
					g_created_by,
				
					g_payment_type,
				
					CAST(g_amount AS SIGNED) amount,
				
					g_trans_no,g_parent_trans_no,
				
					g_trans_error,
				
					g_created_date,
				
					g_merchant_trans_no
				
					FROM gp_admin.gm_payment_reversed WHERE g_created_date BETWEEN DATE_ADD(CURDATE(),INTERVAL -1 DAY) AND CURDATE()')
				
					                    FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX) ) +N' 
				
				g_payment_gateway_no
				
				g_created_by 
				
				g_payment_type
				
				amount
				
				g_trans_no
				
				g_parent_trans_no
				
				g_trans_error
				
				g_created_date
				
				g_merchant_trans_no
				
				
				
				
				
				
				
				
				
				
				
				
				
				
				
				
				
				
				
				' ;
                +N'
                +N'
                +N'
                +N'
                +N'
                +N'
                +N'
                +N'
                +N'
                +CAST (
                    ( select td = g_payment_gateway_no,
        '',td = g_created_by,
        '',td = g_payment_type,
        '',td = amount,
        '',td = g_trans_no,
        '',td = g_parent_trans_no,
        '',td = g_trans_error,
        '',td = convert(varchar(19),g_created_date,121),
        '',td = g_merchant_trans_no from openquery(GAME321_ADMIN,'SELECT g_payment_gateway_no,
g_created_by,
g_payment_type,
CAST(g_amount AS SIGNED) amount,
g_trans_no,g_parent_trans_no,
g_trans_error,
g_created_date,
g_merchant_trans_no
FROM gp_admin.gm_payment_reversed WHERE g_created_date BETWEEN DATE_ADD(CURDATE(),INTERVAL -1 DAY) AND CURDATE()')
                    FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX) ) +N'
-- 发送邮件
                            EXEC msdb.dbo.sp_send_dbmail @profile_name='mail config', -- 邮件配置文件名
                            @recipients=@smail, -- 发送邮件地址
                            @copy_recipients=@cmail, -- 抄送邮件地址
                            @subject = @title, -- 邮件标题
                            @body = @tableHTML, -- 邮件内容
                            @body_format = 'HTML' ;

运行格式
exec dbo.sp_email '发送邮件地址1;发送邮件地址2','抄送邮件地址1;抄送邮件地址2'