Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL DB Email sending,HTML body formatting from two tables Expand / Collapse
Author
Message
Posted Sunday, July 28, 2013 6:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 28, 2013 11:43 AM
Points: 1, Visits: 5
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


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

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.
Post #1478361
Posted Monday, July 29, 2013 11:43 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, June 9, 2016 6:49 AM
Points: 1,770, Visits: 517
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

Post #1478687
Posted Tuesday, May 27, 2014 7:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 7:51 AM
Points: 2, Visits: 0
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!!
Post #1574789
Posted Tuesday, May 27, 2014 8:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 7:51 AM
Points: 2, Visits: 0
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.
Post #1574796
Posted Tuesday, January 19, 2016 10:34 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, February 12, 2016 2:16 AM
Points: 78, Visits: 568
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
Post #1753776
Posted Tuesday, January 19, 2016 10:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:57 AM
Points: 7,646, Visits: 10,605
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1753781
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse