SQL DB Email sending,HTML body formatting from two tables

  • I am working on a sql server DB mail sending task in which the mail body should be as HTML. Data has to be pulled from two different table.

    Ex.

    Table -1[Staging table-Rows keeps on adding]

    ID Name C_Name

    1 john Mumbai

    2 Adam pune

    3 Kevin Delhi

    Table -2[Static table,config kind]

    FieldID FieldName FieldOrder

    1 CustomerName 1

    2 City 2

    As a first step-1 i need a select query which will return the below resultset:[Struck here]

    CustomerName John Adam Kevin

    City Mumbai Pune Delhi

    Step-2 frame the HTML table from the above resultset:[This should be easy if the above resultset is ready]

    <table >

    <tr>

    <td>

    CustomerName</td>

    <td>

    john</td>

    <td>

    Adam</td>

    <td>

    Kevin</td>

    </tr>

    <tr>

    <td>

    City</td>

    <td>

    Mumbai</td>

    <td>

    pune</td>

    <td>

    Delhi</td>

    </tr>

    Table rows continues....

    </table>

    Struggling for an optimized query ,please suggest.

  • Try something like this, replacing the query:

    DECLARE @tableHTML NVARCHAR(MAX) ;

    DECLARE @sqlquery VARCHAR(MAX)

    SET @tableHTML =

    N'<H1>PLACE TABLE HEADER HERE</H1>' +

    N'<table border="1">' +

    N'<tr><th>COLUMN 1 HEADER</th>' +

    N'<th>COLUMN 2 HEADER</th>' +

    N'<th>COLUMN 3 HEADER</th> </tr>' +

    --place sql here formatted like this

    CAST ( ( SELECT td = COLUMN1 0 END), ' ',

    td = COLUMN2, ' ',

    td = COLUMN3, ' '

    FROM DATABASE_Name..Table_Name

    FOR XML PATH('tr'), TYPE

    ) AS NVARCHAR(MAX) ) +

    N'</table>' ;

    EXEC msdb.dbo.sp_send_dbmail @recipients='Someone@yahoo.com',

    @subject = 'Place Subject Here',

    @body = @tableHTML,

    @body_format = 'HTML',

    @query = @sqlquery

  • Now, I am new to SQL and in the phase of learning while doing. I am emailing results from a query via an HTML table generated by code much like what you have shown.

    It works perfectly fine and I am a little proud of myself for getting this far. Next step is my task - the consumer wants the data in two different html tables in one email. Is that possible with this?

    I've tried doing

    @body = @tableHTMLSQL AND @tableHTMLORACLE,

    but alas that was to no great success. Again I am new so this may be a simple fix but its not obvious to me.

    Thanks in advance!!

  • I actually figured it out a moment after posting. For anyone in a similar sitation I did the following:

    DECLARE @tableHTMLA VARCHAR(MAX) ;

    SET @tableHTMLA =

    N'<H1>PLACE TABLE HEADER HERE</H1>' +

    N'<table border="1">' +

    N'<tr><th>COLUMN 1 HEADER</th>' +

    N'<th>COLUMN 2 HEADER</th>' +

    N'<th>COLUMN 3 HEADER</th> </tr>' +

    CAST ( ( SELECT td = COLUMN1 0 END), ' ',

    td = COLUMN2, ' ',

    td = COLUMN3, ' '

    FROM DATABASE_Name..Table_Name

    FOR XML PATH('tr'), TYPE

    ) AS NVARCHAR(MAX) ) +

    N'</table>' ;

    DECLARE @tableHTMLB VARCHAR(MAX) ;

    SET @tableHTMLB =

    N'<H1>PLACE TABLE HEADER HERE</H1>' +

    N'<table border="1">' +

    N'<tr><th>COLUMN 1 HEADER</th>' +

    N'<th>COLUMN 2 HEADER</th>' +

    N'<th>COLUMN 3 HEADER</th> </tr>' +

    CAST ( ( SELECT td = COLUMN1 0 END), ' ',

    td = COLUMN2, ' ',

    td = COLUMN3, ' '

    FROM DATABASE_Name..Table_Name

    FOR XML PATH('tr'), TYPE

    ) AS NVARCHAR(MAX) ) +

    N'</table>' ;

    DECLARE @body2 NVARCHAR(MAX) ;

    SET @body2 = @tableHTMLA + @tableHTMLB ;

    EXEC msdb.dbo.sp_send_dbmail @recipients='Someone@yahoo.com',

    @subject = 'Place Subject Here',

    @body = @body2,

    @body_format = 'HTML',

    This may be a very round about way to do this ut it got me the exact results I needed so I am happy.

  • Hi Everyone,

    I have a query. I can combine 3 table results in HTML body. When I add my fourth table, I get an error. This error is not respect to the new table added to the body.

    I don't get this error when I remove the fourth table from the HTML body.

    Can you please help, if there is any constraint that only a specific number of tables canbe added to the HTML body?

    Thanks in advance,

    Priya

    Regards
    Priya

  • Hi Priya,

    You are posting your question on a topic that is one and a half years old. Since the question is only very loosely related, that can be confusing. It can also cause people to simply not see your question.

    I suggest opening a new topic for your question. And when you do that, please also provide a lot more information. First, we need to know what error you got, so please copy and paste the error message. Second, we need to be able to recreate the circumstances, so please post a full repro scripts (that is, CREATE TABLE statements to set up the tables, either your real tables or anonimyzed demo tables, INSERT statements to supply some sample data, the SELECT statement that works, and the modified version that doesn't work.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • take a look at vsql-email (sql-email.com), is very simple to use and it saved me a lot of time

  • Thanks for this! It fixed my problem too.

  • gwalton1234 wrote:

    Thanks for this! It fixed my problem too.

    This looks like a prelude to spam.  Which problem of your did it fix and which of the many entries fixed it?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I was trying to figure out how to add multiple tables to the dbmail email code. spamfaux3066's post was the clue to what I needed. I have implemented that fix to multiple emails that needed this. It works great now.

    Thanks.

    Geoff

  • Awesome.  Thank you for the feedback and welcome aboard!

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply