Hello All!
So I learned this cool thing today called Database Mail. I have a statement that will allow me to perform a query, and send a email with the results in a table form in a HTML format. What I would like to do is place more than one table. I have several tables to send, I don't want to send 5 emails. I want to send 1 email with 5 tables in it. Here some code!
TIA!
Rudy
DECLARE @.xml NVARCHAR(MAX)DECLARE @.body NVARCHAR(MAX)
BEGIN
SET @.xml =CAST(( SELECT Service_Date_Time AS 'td'
FROM [Pharm Test Local].dbo.Active_Orders WHERE (Service_Date_Time <= dateadd( Month, -9, getdate())) GROUP BY Service_Date_Time FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @.body ='<html><H1>Records that have expired.</H1><body bgcolor=White><table border = 2><tr><th>I-9</th></tr>' SET @.body = @.body + @.xml +'</table></body></html>'
SET @.xml =CAST(( SELECT Service_Date_Time AS 'td'
FROM [Pharm Test Local].dbo.Archive_Orders WHERE (Service_Date_Time <= dateadd( Month, -2, getdate())) GROUP BY Service_Date_Time FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @.body ='<html><H1>Records that have expired1.</H1><body bgcolor=White><table border = 2><tr><th>Workers Comp</th></tr>' SET @.body = @.body + @.xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@.recipients=N'jrudolf@.ikon.com',
@.body= @.body,
@.body_format = 'HTML',
@.Subject='HR Records to delete',
@.profile_name = 'Ikon'
end
YOu can just combine them. You can also use UNION/All to combine them into a single query.
DECLARE @.xml NVARCHAR(MAX)DECLARE @.body NVARCHAR(MAX)
BEGIN
SET @.xml =CAST(( SELECT Service_Date_Time AS 'td'
FROM [Pharm Test Local].dbo.Active_Orders WHERE (Service_Date_Time <= dateadd( Month, -9, getdate())) GROUP BY Service_Date_Time FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
+CAST(( SELECT Service_Date_Time AS 'td'
FROM [Pharm Test Local].dbo.Archive_Orders WHERE (Service_Date_Time <= dateadd( Month, -2, getdate())) GROUP BY Service_Date_Time FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @.body ='<html><H1>Records that have expired1.</H1><body bgcolor=White><table border = 2><tr><th>Workers Comp</th></tr>' SET @.body = @.body + @.xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@.recipients=N'jrudolf@.ikon.com',
@.body= @.body,
@.body_format = 'HTML',
@.Subject='HR Records to delete',
@.profile_name = 'Ikon'
end
|||Thanks Oj! It worked great!
Rudy
No comments:
Post a Comment