SQL Galaxy (8/24/2016)
I have received users report in email with respective data in column order in one database.The following include two different query in different database for fetching data. but i received email first part data only not comes second part query data separately.
Pl. help. how will merged these two query data in single email report and display email body separately.
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
SET @xml = CAST(( SELECT [user_id] AS 'td','',[USER_NAME] AS 'td','',
[USER_DETAILS] AS 'td','', [description] AS 'td','', [Active] AS 'td',''
FROM [iTAS_RIL_JN_RAIL].[MSTR].[Users] AS A WHERE A.Active = 'Y' and A.user_id
NOT IN (SELECT distinct [USER_ID]
FROM [iTAS_RIL_JN_RAIL].[TRANS].[USER_LOGGED_IN_HISTORY]
where LOGGED_IN_TIME > GETDATE()-30)
order by A.user_id asc
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><body><H3>iTAS-RAIL Users</H3>
<table border = 1>
<tr>
<th> user_id </th> <th> USER_NAME </th> <th> USER_DETAILS </th> <th> Description </th> <th> Active </th> </tr>'
DECLARE @xml1 NVARCHAR(MAX)
DECLARE @body1 NVARCHAR(MAX)
SET @xml1 = CAST(( SELECT [user_id] AS 'td','',[USER_NAME] AS 'td','',
[USER_DETAILS] AS 'td','', [description] AS 'td','', [Active] AS 'td',''
FROM [iTAS_RIL_JN_RAIL].[MSTR].[Users] AS A WHERE A.Active = 'Y' and A.user_id
NOT IN (SELECT distinct [USER_ID]
FROM [iTAS_RIL_JN_ROAD].[TRANS].[USER_LOGGED_IN_HISTORY]
where LOGGED_IN_TIME > GETDATE()-30)
order by A.user_id asc
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body1 ='<html><body><H3>iTAS-ROAD Users</H3>
<table border = 1>
<tr1>
<th> user_id </th> <th> USER_NAME </th> <th> USER_DETAILS </th> <th> Description </th> <th> Active </th> </tr1>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'db_mail', -- replace with your SQL Database Mail Profile
@body = @body,
@body_format ='HTML',
@recipients = 'anauses@fectsec.com', -- replace with your email address
@subject = 'Application Unused Users';
Look at your code and show me where you have included @body1 and @xml1 as part of the @body that gets sent via the email. You can't because you haven't. 😉 The reason why you only get the first part in the email is simple... although you've calculated the second part, you've simply not included it in the email body.
--Jeff Moden
Change is inevitable... Change for the better is not.