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