July 28, 2013 at 6:47 am
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.
July 29, 2013 at 11:43 am
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
May 27, 2014 at 7:56 am
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!!
May 27, 2014 at 8:04 am
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.
January 19, 2016 at 10:34 am
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
January 19, 2016 at 10:47 am
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.
November 2, 2016 at 8:25 am
take a look at vsql-email (sql-email.com), is very simple to use and it saved me a lot of time
April 18, 2022 at 8:31 pm
Thanks for this! It fixed my problem too.
April 19, 2022 at 5:00 am
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
Change is inevitable... Change for the better is not.
April 19, 2022 at 2:38 pm
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
April 19, 2022 at 2:49 pm
Awesome. Thank you for the feedback and welcome aboard!
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply