HTML inside a mail - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

HTML inside a mail

Author

Topic

skybvi
Posting Yak Master

193 Posts

Posted - 2012-02-06 : 12:33:59

Hi,
I am using hte code :-

IF ((SELECT COUNT(*) FROM #temp) > 0)
BEGIN
declare @tablebody nvarchar(1000),
@to nvarchar(max),
@cc nvarchar(max)

Set @to = 'syadav@xyz.com'
Set @cc = 'syadav@xyz.com'

SET @tablebody =
N'<H3>These below items are incorrect in the following stores.</H3>' +
N'<table border="1">' +
N'<tr><th>STORE_NAME</th>
<th>UPD_USER_ID</th>
<th>UPC</th>
<th>GP_ITEM#</th>
<th>LONG_POS_DESC</th>
<th>POS_DESC</th>
<th>Retail_Price</th>
<th>Case_Price</th>
<th>UNIT_CASE</th>
<th>DT_PLU_LAST_UPDATE</th>
<th>UPD_ORIGIN</th>
</tr>' +

CAST ((SELECT td=STORE_NAME, '', td=UPD_USER_ID,'', td=cast(UPC as bigint),'',
td = GP_ITEM#, '',td =LONG_POS_DESC, '',
td = POS_DESC, '', td = convert(nvarchar(20),Retail_Price, 1), '',
td = convert(nvarchar(20),Case_Price, 1), '',
td = cast(UNIT_CASE as nvarchar(10)),'',
td = cast(DT_PLU_LAST_UPDATE as nvarchar(20)),'',td=UPD_ORIGIN,''
FROM #temp
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(1000) ) +
N'</table>'

EXEC msdb.dbo.sp_send_dbmail
@recipients = @to,
@copy_recipients = @cc,
@body = @tablebody,
@body_format = 'HTML',
@subject = 'Retail Items having incorrect data',
@profile_name = 'SQL AGENT'

-- I am getting too big column name and the data, how to decrease the size of the data inside the mail.

Regards,
Sushant
DBA
Virgin Islands(U.K)

netraju
Starting Member

4 Posts

Posted - 2012-02-07 : 01:46:59

correct the variable declaration as below

declare @tablebody nvarchar(max)
declare @to nvarchar(max)
declare @cc nvarchar(max)

Thanks,
Raj Singh
Follow me at http://netraju.blogspot.com

skybvi
Posting Yak Master

193 Posts

Posted - 2012-02-07 : 07:47:54

Does that even matter .

The sending e mail thing... Is working fine without that.
I just need to decrease the font size of the data inside the table.

Regards,
Sushant
DBA
Virgin Islands(U.K)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources