Populate HTML Email Template with Data from SQL Server Query via Orchestrator

Question

Populate HTML Email Template with Data from SQL Server Query via Orchestrator

1.8k Views Asked by MrM21632 At 30 October 2017 at 16:34

I am attempting to set up a process in Orchestrator that will generate and send an email from an HTML template. However, I also want to be able to add data to that template from the results of a SQL Server query.

For example, here is a section of the template where I want to add data:

<tr>
    <td align="right" valign="top">
        <font color="#2684AB" face="arial" />
        File:
    </td>
    <td align="left" valign="top">
        <font color="#2684AB" face="arial" />
        &nbsp; <!-- Data goes here (field is named FileName) -->
    </td>
</tr>

The only problem is that I'm not sure how to go about this. I know how to call the query, but I don't know how to add the data to the template. If this helps at all, I also know all of the fields where I am pulling data from. Any suggestions? I should also add that I am relatively new to working in Orchestrator.

Original Q&A

There are 1 best solutions below

**Nolan Shang** · Answer 1 · 2017-10-31T01:47:27.500000

It's my code sample:

      SET @tableHTML =@tableHTML+'<h3>'+@Title+N'</h3>' +
            N'<tr style="background-color: #248157;color: white;height: 40px;">
            <th style="border: 1px solid #ccc">Ext PONo</th><th style="border: 1px solid #ccc">Order ID</th>
            <th style="border: 1px solid #ccc">Client</th><th style="border: 1px solid #ccc">Amount</th><th style="border: 1px solid #ccc">Order Date</th>
            <th style="border: 1px solid #ccc">Status</th><th style="border: 1px solid #ccc">Hold Reason</th>
            <th style="border: 1px solid #ccc">Hold User</th><th style="border: 1px solid #ccc">Hold Date</th>
            </tr>'+CONVERT(NVARCHAR(max),(
                SELECT '<tr style='+CASE WHEN ROW_NUMBER()OVER(ORDER BY OrderID)%2=0 THEN  '"background-color: #ffffff;"' ELSE '"background-color: #f2f2f2;"' END +'>'
                      ,[td/@style]='border: 1px solid #ccc',td=ExtPO,''
                      ,[td/@style]='border: 1px solid #ccc',td='<a href=http://xxxxxxxxxxxx='+LTRIM(OrderID)+'>'+LTRIM(OrderID)+'</a>',''
                      ,[td/@style]='border: 1px solid #ccc',td=LTRIM(ClientID)+'-'+ClientName,''
                      ,[td/@style]='border: 1px solid #ccc',td=ISNULL(SubTotal,0),''
                      ,[td/@style]='border: 1px solid #ccc',td=ISNULL(CONVERT(VARCHAR,OrderDate,101),'')+'<BR><font color=red>Held '+LTRIM(HeldCount)+' time(s)</font>',''
                      ,[td/@style]='border: 1px solid #ccc',td=OrderStatus+ISNULL('<br><font color=red>In Flag:'+FlagStatus+'</font>',''),''
                      ,[td/@style]='border: 1px solid #ccc',td=CASE WHEN HoldReasonCode='290' THEN REPLACE(HoldReason,'!',' of <a href=http://xxxxxxxxx?orderID='+LTRIM(ParentOrderID)+'>'+LTRIM(ParentOrderID)+'</a>!') ELSE HoldReason END+ISNULL('<BR><font color=red>'+Comments+'</font>','')  ,''
                      ,[td/@style]='border: 1px solid #ccc',td=HoldUser,''
                      ,[td/@style]='border: 1px solid #ccc',td=ISNULL(CONVERT(VARCHAR,HoldDate,101),''),'','</tr>'
                FROM #HeldOrder WHERE SourceCode=@SourceCode ORDER BY HoldReasonCode
                FOR XML PATH(''),TYPE
            ))+N'</table>'



IF LEN(@tableHTML)>0
    BEGIN
        SET @HTMLContent='<!DOCTYPE html>
        <html><head>
        <style>
        table, td, th {border: 1px solid #ccc;font-family:Cambria}
        table {border-collapse: collapse;width: 100%;}
        th {background-color: #248157;color: white;height: 40px}
        td {height: 40px;vertical-align: middle;}
        .even {background-color: #f2f2f2;}
        .odd {background-color: #ffffff;}
        tr:nth-child(even) {background-color: #f2f2f2}
        </style></head>
        <body>

        <p> </p>'
        SET @HTMLContent=@HTMLContent+@tableHTML+'<BR>'+CONVERT(VARCHAR,GETDATE())+'</body></html>'

Populate HTML Email Template with Data from SQL Server Query via Orchestrator

There are 1 best solutions below

Related Questions in HTML

Related Questions in SQL-SERVER

Related Questions in SYSTEM-CENTER

Trending Questions

Popular # Hahtags

Popular Questions