SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL DB Email sending,HTML body formatting from two tables


SQL DB Email sending,HTML body formatting from two tables

Author
Message
suthaharmca
suthaharmca
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 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.
Chris Hurlbut
Chris Hurlbut
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1850 Visits: 540
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


spamfaux3066
spamfaux3066
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 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!!
spamfaux3066
spamfaux3066
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 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.
MSSQLBuddy
MSSQLBuddy
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 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
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10886 Visits: 11980
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
welcome.to.route66
welcome.to.route66
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 3
take a look at vsql-email (sql-email.com), is very simple to use and it saved me a lot of time
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search