主页 / dba / 问题 / 83776
Accepted
Heisenberg
Asked: 2014-11-28 00:30:16 +0800 CST

需要在 Sql Server 2008 R2 中通过数据库邮件发送格式化的 HTML 电子邮件

  • 772

我的要求是将自动电子邮件创建为（通过数据库邮件格式化的 HTML 电子邮件）基本要求已经完成，当触发作业时，它将数据插入表中，并且该信息已计划作为电子邮件发送，但现在客户已经要求以这种格式。

我所做的当前格式

 PO Date:2014-11-26 PO ID:PO1 SAP Ref:0001106102 GRN:1067 DealerID:045 Dealer Name:ABC(Pvt)Ltd. 2 Status:New

客户要求的格式，类似格式的表格

PO Date |PO ID| SAP Ref| GRN|DealerID|Dealer Name|Status

这是我用来将数据插入到 Email_Table(_ERROR_MAIL) 的 SQL 查询，这需要根据要求进行垂涎 

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ImpCancelledGRN')
BEGIN
    DROP PROCEDURE [dbo].[ImpCancelledGRN]
END
GO


CREATE PROCEDURE [dbo].[ImpCancelledGRN] 
WITH ENCRYPTION
AS

SET NOCOUNT ON
BEGIN
BEGIN TRY
    -- Begin transaction
    BEGIN TRAN

            INSERT INTO _ERROR_MAIL(Recipients, Subject, CreationDate, IsNew, LastModifiedBy, Body)
            SELECT '[email protected]', 'SAP CANCELLED GRN', GETDATE(), 1, 'sapws',
                'PO Date:' + CONVERT(VARCHAR(10),P.Date,120) + 
                ' PO ID:' + P.ID + 
                ' SAP Ref:' + P.ID2 + 
                ' GRN:' + G.ID + 
                ' Dealer ID:' + D.ID + 
                ' Dealer Name:' + D.Name + 
                ' Status:' + CASE WHEN G.SubmittedDate IS NULL THEN 'New' ELSE 'Dealer Submitted' END
            FROM I_CancelledGRN I
                INNER JOIN TxnGRN G ON G.ID = I.ID
                INNER JOIN Distributor D ON D.UID = G.DistributorUID
                INNER JOIN POTxn P ON P.SiteUID = G.POTxn_SiteUID AND P.UID = G.POTxnUID
            WHERE IsCancelled IS NULL;

            UPDATE TxnGRN 
            SET ExpiryDate = GETDATE() 
                , SAPCancellationDate = I.Date
                , SAPCancelledBy = 'SAP'
                , IsCancelled = 1
            FROM I_CancelledGRN I
                INNER JOIN TxnGRN G ON G.ID = I.ID
            WHERE IsCancelled IS NULL;

    -- Commit transaction
    COMMIT TRAN 

END TRY
BEGIN CATCH
    -- Get error message, severity and satus information
    DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT;
    SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(),    @ErrorState = ERROR_STATE();
    -- Rollback transaction
    ROLLBACK TRAN;
    -- Log error message details
    INSERT INTO _ERROR_LOG(Module, SubModule, Text, Date)
    VALUES('SAP', '[ImpCancelledGRN]', @ErrorMessage, GETDATE());
    -- Raise error
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH
RETURN 0;   
END
SET NOCOUNT OFF
GO
sql-server sql-server-2008-r2

2 个回答

  1. Best Answer

    在这里，您如何创建邮件的 HTML 正文部分。

    步骤1：

     DECLARE @Body NVARCHAR(MAX),
    @TableHead VARCHAR(1000),
    @TableTail VARCHAR(1000)



SET @TableTail = '</table></body></html>' ;
SET @TableHead = '<html><head>' + '<style>'
    + 'td {border: solid black;border-width: 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font: 11px arial} '
    + '</style>' + '</head>' + '<body>' + 'Report generated on : '
    + CONVERT(VARCHAR(50), GETDATE(), 106) 
    + ' <br> <table cellpadding=0 cellspacing=0 border=0>' 
    + '<tr> <td bgcolor=#E6E6FA><b>PO Date</b></td>'
    + '<td bgcolor=#E6E6FA><b>PO ID</b></td>'
    + '<td bgcolor=#E6E6FA><b>SAP Ref</b></td>'
    + '<td bgcolor=#E6E6FA><b>GRN</b></td>'
    + '<td bgcolor=#E6E6FA><b>DealerID</b></td>'
    + '<td bgcolor=#E6E6FA><b>Dealer Name</b></td>'
    + '<td bgcolor=#E6E6FA><b>Status</b></td></tr>' ;

SET @Body = ( SELECT    td = CONVERT(VARCHAR(10), P.Date, 120), '',
                        td = P.ID, '',
                        td = P.ID2, '',
                        td = G.ID, '',
                        td = D.ID,'',
                        td = D.Name,'',
                        td = CASE WHEN G.SubmittedDate IS NULL THEN 'New'
                                  ELSE 'Dealer Submitted'
                             END, ''
              FROM      I_CancelledGRN I
                        INNER JOIN TxnGRN G ON G.ID = I.ID
                        INNER JOIN Distributor D ON D.UID = G.DistributorUID
                        INNER JOIN POTxn P ON P.SiteUID = G.POTxn_SiteUID
                                              AND P.UID = G.POTxnUID
              WHERE IsCancelled IS NULL
            FOR   XML RAW('tr'),
                  ELEMENTS
            )



SELECT  @Body = @TableHead + ISNULL(@Body, '') + @TableTail

    第 2 步： 创建数据库电子邮件配置文件

    第 3 步：发送电子邮件

    EXEC sp_send_dbmail 
  @profile_name='DatabaseEmailProfile',
  @copy_recipients ='[email protected]',
  @recipients='[email protected]',
  @subject='Query Result',
  @body=@Body ,
  @body_format = 'HTML' ;
    • 15

  2. 我的用户正在寻找“XML PATH”解决方案并尝试使用它，但他们不是程序员，因此感到困惑。我没有尝试帮助他们学习使用 XML PATH，而是创建了一个过程，该过程采用表的名称并返回包含 HTML 标记的字符串，以将表显示为 HTML 表。我的解决方案从数据源动态地派生 HTML 表的列名。

    程序 HtmlTable 的示例使用

    CREATE table ##foo (bar1 int, bar2 varchar(20), bar3 datetime)
INSERT into ##foo values (1, 'Abcdef', getdate())
INSERT into ##foo values (2, 'Ghijkl', '05/05/15')
DECLARE @tableHtml varchar(max)
EXEC dbo.HtmlTable
    '##foo',
    @tableHtml output
PRINT @tableHtml

    @tableHtml 变量仅包含 HTML 表格的标记，适合包含在电子邮件正文中。要单独查看，您需要使用 html、head 和 body 标签包装输出。

    过程 HtmlTable 的输出

    @tableHtml 的内容

    程序 HtmlTable 的代码

    /*
Author: Leigh Haynes
Date: February 2015
Notes: Takes a table name as string parameter and returns a string that contains HTML markup to display the table contents as an HTML table.

The input table should be sorted before invoking HtmlTable.

*/

CREATE PROCEDURE [dbo].[HtmlTable]
    @data_source varchar (100) = NULL,
    @tableHTML varchar(max) OUTPUT
AS

BEGIN    

SET NOCOUNT ON;

DECLARE 
    @db varchar(50), 
    @table varchar(100), 
    @cmd varchar(400), 
    @rcd_cnt int,
    @sql nvarchar(1000);

--use procedure DataSourceCheck to see if @data_source is valid
EXEC dbo.DataSourceCheck @data_source, @db output, @table output;

IF @db is NULL --if the data source is not good, @db comes back NULL, and @table holds info as to the problem (either the table does not exist, or it is empty).
BEGIN
    SET @tableHtml = @table;
    RETURN;
END;

--We have a good table. Use information_schema metadata for table to get column names.
IF OBJECT_ID ('tempdb..##columnNames') IS not null DROP TABLE ##columnNames;
CREATE table ##columnNames (column_name varchar(50), position int identity);

SET @sql = 'USE ' + @db + '; INSERT into ##columnNames SELECT column_name from information_schema.columns where table_name = ''' + @table + ''' order by ordinal_position';
EXEC master.sys.sp_executesql @sql;

--use ##columnNames to create table ##columnPivot with the proper number of fields to hold data
IF OBJECT_ID ('tempdb..##columnPivot') IS not null DROP TABLE ##columnPivot;
CREATE table ##columnPivot (f1 varchar(200));

DECLARE 
    @i int = 2,
    @fieldct int, 
    @column varchar(50), 
    @field varchar(200),
    @value varchar(100), 
    @html varchar(max) = '';

SET @fieldct = (SELECT COUNT(*) from ##columnNames);
WHILE @i <= @fieldct --loop through adding a field to ##columnPivot for each column. Max field len is 200.
BEGIN
    SET @sql = 'ALTER table ##columnPivot ADD f' + cast (@i as varchar(2)) + ' varchar(200)';
    EXEC master.sys.sp_executesql @sql;
    SET @i = @i + 1;
END
--##columnPivot is constructed but empty. Columns are named f1, f2, f3, etc

--construct dynamic SQL string that will be executed to populate ##columnPivot
SET @sql = 'INSERT into ##columnPivot SELECT ';
SET @i = 1;
SET @fieldct = (SELECT count(*) from ##columnNames);

WHILE @i <= @fieldct - 1
BEGIN
    SET @column = (SELECT top 1 column_name from ##columnNames where position = cast (@i as varchar(2)));
    SET @field = 'CAST([' + @column + '] as varchar(200)),';
    SET @sql = @sql + @field;
    SET @i = @i + 1;
END

SET @column = (SELECT top 1 column_name from ##columnNames where position = @fieldct);
SET @field = 'CAST([' + @column + '] as varchar(200)) FROM ' + @data_source;
SET @sql = @sql + @field; --@sql now contains the SQL statement that will insert data from @data_source into ##columnPivot

--execute @sql to insert into ##columnPivot the data from @data_source table
EXEC master.sys.sp_executesql @sql;

--format the output
IF OBJECT_ID ('tempdb..#columns') IS not null DROP TABLE #columns;
--use a copy of ##columnNames, because next steps delete from this table, and ##columnNames data is needed below. Does not need to be a global temp.
SELECT *
into #columns
from ##columnNames
order by position;

SET @fieldct = (SELECT count(*) from #columns);
SET @i = 1;

--create the header row for the table containing column names from the @data_source
WHILE @i <= @fieldct 
BEGIN
    SET @field = (SELECT top 1 column_name from #columns order by position);
    SET @html = @html + '<td bgcolor="#dedede"><b>' + @field + '</b></td>';
    SET @i = @i + 1;
    DELETE from #columns where column_name = @field;
END

SET @html = '<tr>' + @html + '</tr>'; --now @html contains the header row of the output table


--populate ##columnPivot by working through the data row by row. 
ALTER table ##columnPivot add id_key int identity;

DECLARE 
    @j int = 1, 
    @fieldcnt int, 
    @cell varchar(100), 
    @row varchar(500) = '';

SET @i = 1;
SET @fieldcnt = (SELECT count(*) from ##columnNames);
SET @rcd_cnt = (SELECT count(*) from ##columnPivot);

WHILE @i <= @rcd_cnt --this loop executes one time for each row of data
BEGIN
    SET @j = 1;
    WHILE @j <= @fieldcnt --this loop executes one time for each column (cell) of data
    BEGIN
        SET @sql = 'SELECT @value = f' + cast (@j as varchar(2)) + ' from ##columnPivot where id_key = ' + cast (@i as varchar(2));
        EXEC master.sys.sp_executesql @sql, N'@value varchar(200) OUTPUT', @value OUTPUT;
        SET @cell = '<td>' + ISNULL (@value, '<br>') + '</td>'; --need to use <br> if the cell is empty
        SET @row = @row + @cell;
        SET @j = @j + 1;
    END
    SET @row = '<tr>' + @row + '</tr>';     
    SET @html = @html + @row;
    SET @row = '';
    DELETE from ##columnPivot where id_key = cast (@i as varchar(2));
    SET @i = @i + 1;
END

SET @tableHTML = '<table border="1" cellspacing="0" cellpadding="5">' + @html + '</table><br>'; 

END

    请注意，我正在调用过程“DataSourceCheck”来确定参数@data_source 包含有效表的名称。DataSourceCheck 不必是一个单独的过程——SQL 可以直接包含在 HtmlTable 过程中，但为了模块化和可重用性，我将它分开。

    过程 DataSourceCheck 的示例使用

    CREATE table ##foo (bar1 int, bar2 varchar(20), bar3 datetime)
INSERT into ##foo values (1, 'Abcdef', getdate())
INSERT into ##foo values (2, 'Ghijkl', '05/05/15')
DECLARE @table varchar(200), @db varchar(50)
EXEC dbo.DataSourceCheck
    '##foo', 
    @db output,
    @table output
PRINT @db
PRINT @Table

    输出

    tempdb
##foo

    现在放下桌子，看看会发生什么：

    DROP table ##foo

DECLARE @table varchar(200), @db varchar(50)
EXEC dbo.DataSourceCheck
    '##foo', 
    @db output,
    @table output
PRINT @db
PRINT @Table

    输出

    <br>Table ##foo does not exist or is improperly qualified.<br>

    过程 DataSourceCheck 的代码

    /*
Author: Leigh Haynes
Date: February 2015
Notes: Called by HtmlTable and CreateCsvFile to check validity of data source that is going to turn into an HTML table or a CSV file.
*/

CREATE PROCEDURE [dbo].[DataSourceCheck] 
    @dataSource varchar (100) = NULL,
    @db varchar(50) = NULL output,
    @table varchar(100) = NULL output

AS

BEGIN

DECLARE 
    @buffer varchar(100),
    @object varchar(100),
    @objectId bigint,
    @schema varchar(50),
    @rcd_cnt int,
    @tableHtml varchar(200),
    @sql nvarchar(1000)

SET @buffer = @dataSource;

--cannot accesss a local temp table. Return.
IF SUBSTRING (@buffer, 1, 1) = '#' and SUBSTRING (@buffer, 2, 1) <> '#'
BEGIN
    --use LEFT 25 to make sure the local temp table name isn't too long for the @table varchar(100) variable.
    SET @table = '<br>Table ' + LEFT (@dataSource, 25) + ' is a local temp table. Must use a global temp or permanent table.<br>';
    RETURN;
END;

--set up the object name in the right format so you can check the OBJECT_ID
ELSE IF (SUBSTRING (@buffer, 1, 2) = '##')
BEGIN
    SET @db = 'tempdb';
    SET @table = @dataSource;
    SET @object = @db + '..' + @table; --need to include tempdb so OBJECT_ID finds the temp table
END;
ELSE
BEGIN
    --deal with schema
    SET @db = SUBSTRING (@buffer, 1, charindex ('.', @buffer) - 1);
    SET @buffer = replace (@buffer, @db + '.', '');
    IF SUBSTRING (@buffer, 1, 1) = '.' 
    BEGIN
        SET @schema = '..';
        SET @buffer = replace (@buffer, '.', '');
    END
    ELSE 
    BEGIN
        SET @schema = SUBSTRING (@buffer, 1, charindex ('.', (@buffer)) - 1);
        SET @buffer = replace (@buffer, @schema + '.', '');
    END
    SET @table = @buffer;
    SET @object = @dataSource;
END;

--does our data source exist? Check the object_id. If object does not exist, return.
SET @objectId = OBJECT_ID (@object, 'U');
IF @objectId is NULL 
BEGIN
    SET @db = NULL;
    SET @table = '<br>Table ' + @dataSource + ' does not exist or is improperly qualified.<br>';
    RETURN;
END;

--we have a valid data source. Check that it has rows and notify if empty.
SET @sql = 'SELECT @rcd_cnt = count(*) from ' + @dataSource;
EXEC master.sys.sp_executesql @sql, N'@rcd_cnt int OUTPUT', @rcd_cnt OUTPUT; 
IF @rcd_cnt = 0 
BEGIN
    SET @db = NULL;
    SET @table = '<br>Table ' + @dataSource + ' is empty.<br>';
    RETURN;
END;

END

    我还有一个 HtmlTable 版本，可以从表（而不是 HTML 表）创建 CSV 文件。

    • 3

相关问题