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';
Simply repeating yourself is not the same thing as clarifying your question.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/