July 21, 2017 at 4:33 pm
i have a table test , i need to send an email in HTML format provided in attachment.CREATE TABLE dbo.test (
StepName VARCHAR(50)
,DBName VARCHAR(50)
,JobId INT
,RunDate DATETIME
)
INSERT INTO test
SELECT 'setp2 '
,'testdb'
,1
,GETDATE()
DECLARE @step_name VARCHAR(50)
,@DBname VARCHAR(50)
,@Jobid VARCHAR(50)
,@run_datetime DATETIME
SELECT @step_name = StepName
,@DBname = DBName
,@Jobid = JobId
,@run_datetime = RunDate
FROM dbo.test
DECLARE @body VARCHAR(2000)
SET @body = ''
SET @Body = @Body + 'Step name= ' + @step_name + @CrLf + 'DB Name = ' + convert(VARCHAR(50), ISNULL(@DBname, '')) + @CrLf + 'J= ' + convert(VARCHAR(50), ISNULL(@Jobid, '')) + @CrLf + 'Run Date = ' + convert(VARCHAR(50), @run_datetime) + @CrLf
EXEC msdb.dbo.sp_send_dbmail @profile_name = '<<Profile Name>>'
,@recipients = 'test@gmail.com'
,@subject = 'Blah, blah'
,@body = @body
July 24, 2017 at 7:01 am
Here's an example of how I built an HTML table to sent via Database Mail in the past. You should be able to adapt it to your purposes.
-- declare variables
DECLARE @table_results nvarchar(max);
;
-- ------------------------------------------------------------------------------------------------
-- html format
-- ------------------------------------------------------------------------------------------------
-- build table
SET @table_results =
N'<h2>Posted Orders</h2>'
+ N'<table border = "1" width = "80%">'
+ N'<tr>'
+ N'<th> Invoice Date </th><th> Order Number </th><th> Item Number </th><th> Extended Total </th><th> Extended Cost </th><th> Quantity </th>'
+ N'</tr>'
+ cast((
SELECT TOP 10
s.invoice_date AS td, '',
s.order_number AS td, '',
s.item_number AS td, '',
s.extended_total AS td, '',
s.extended_cost AS td, '',
s.quantity AS td, ''
FROM dbo.Fact_Sales AS s
WHERE s.invoice_date = '20130610'
FOR XML PATH('tr'), TYPE) AS nvarchar(max))
+ N'</table>'
;
-- send email
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DatabaseMail',
@subject = 'DB Send Mail Test - HTML',
@recipients = 'test@gmail.com',
@body = @table_results,
@body_format = 'HTML'
;
July 24, 2017 at 7:41 am
hey thanks for your reply, my issue i need to display as pivot . Header should be on column 1 and column2 will have corresponding values.
July 24, 2017 at 10:08 am
Here's the SQL to load the variable using your test data. It'll produce a nice HMTL table (you can probably drop the header stuff.).
I'm a little rusty on the pivot / unpivot stuff. Notice I had to convert the data types to match in order to unpivot.
-- declare variables
DECLARE @table_results nvarchar(max);
-- ------------------------------------------------------------------------------------------------
-- html format
-- ------------------------------------------------------------------------------------------------
-- build table
SET @table_results =
N'<h2>Test</h2>'
+ N'<table border = "1" width = "80%">'
+ N'<tr>'
+ N'<th> Setting </th><th> Value </th>'
+ N'</tr>'
+ cast((
SELECT pt.FirstColumn AS td, '',
pt.SecondColumn AS td, ''
FROM (
SELECT StepName,
DBName,
cast(JobId AS varchar(50)) AS JobId,
convert(varchar(50), RunDate, 121) AS RunDate
FROM dbo.test
) AS t
UNPIVOT
(
SecondColumn
FOR FirstColumn IN (StepName, DBName, JobId, RunDate)
) AS pt
FOR XML PATH('tr'), TYPE) AS nvarchar(max))
+ N'</table>'
;
July 24, 2017 at 10:37 am
thanks for your help , i dont want to display setting and value i have removed that from final string . it looks good.
set @table_results = REPLACE (@table_results ,'<tr><th> Setting </th><th> Value </th></tr>','')
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy