Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: Wednesday, November 5, 2014 10:05 AM
Points: 1,549, Visits: 490
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
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse