Query output not display in HTML format

Question

Query output not display in HTML format

SQL Galaxy

One Orange Chip

Points: 29472
More actions
August 22, 2016 at 6:48 am

#311384

Hi.. I have tried and developing one email report from database server.
Email Notification received and some of the column name/data not display properly in email body.
Pl. suggest what is the wrong in this script.
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>'
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 = 'anuses@genpect.com', -- replace with your email address
@subject = 'Application Users';

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

SQL Galaxy (8/22/2016)
Hi.. I have tried and developing one email report from database server.
Email Notification received and some of the column name/data not display properly in email body.
Pl. suggest what is the wrong in this script.
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>'
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 = 'anuses@genpect.com', -- replace with your email address
@subject = 'Application Users';

How could we know? You said you got the email but "some of the column name/data not display properly in email body". What does that mean? What did you get for output? What are you expecting for output? Give us something to work with and we can help find a solution. But as posted there is no chance we can really help here.

_______________________________________________________________

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/

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 2

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';

Sean Lange SSC Guru Points: 286573 More actions · Answer 3

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/

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 5

Hi Jeff.. Thanks as per your suggestion after adding as below line multiple Query result comes in Email body as desired but second set of results not comes is separately.

SET @body = @body + @xml + @body1 + @xml1 + '</table></body></html>'

Pl. help, what need to change in HTML format for display output separately in same email body.

Thanks

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

SQL Galaxy (8/26/2016)
Hi Jeff.. Thanks as per your suggestion after adding as below line multiple Query result comes in Email body as desired but second set of results not comes is separately.
SET @body = @body + @xml + @body1 + @xml1 + '</table></body></html>'
Pl. help, what need to change in HTML format for display output separately in same email body.
Thanks

I don't know what you mean by "second set of results not comes is separately". Please explain.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 7

First and Second query Result set merged with in single HTML report format. but it is not display separately in email body...

Ed Wagner SSC Guru Points: 287024 More actions · Answer 8

The @body should consist of one <html> tag and one <body> tag. Each table should have its own <table> tag and don't forget to close each table with a </table> tag. Once you have your tables built correctly, you can concatenate them into the body of your email.

You can print the contents of your variables to make sure they're valid HTML syntax and for the whole @body to make sure you've concatenated them properly. My guess is that you aren't closing the first HTML table, but it's kind-of hard to follow.

One more piece of advice is to check your table definitions. If any of the columns you're using to build your table are nullable, you'll want to wrap an ISNULL function around the columns in your query to avoid the possibility of getting a NULL body. If you concatenate NULL with anything, the result is NULL.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 9

HTML Heading not coming separately in same email report... both heading merged with in single report.

Thanks

Ed Wagner SSC Guru Points: 287024 More actions · Answer 10

SQL Galaxy (8/26/2016)
HTML Heading not coming separately in same email report... both heading merged with in single report.
Thanks

When you say "both heading merged with in single report" do you mean a single HTML table?

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 11

as per above script both heading are merged in single HTML report.

I want two different HTML report in same email body. Pl give some example HTML script.

Thanks

Ed Wagner SSC Guru Points: 287024 More actions · Answer 12

SQL Galaxy (8/26/2016)
as per above script both heading are merged in single HTML report.
I want two different HTML report in same email body. Pl give some example HTML script.
Thanks

I don't understand what you mean by "single HTML report". If you mean the same HTML table, then close your <table> tag with a </table> tag before your next <table> tag for the second table.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 13

Pl. find the attached email which is receiving in Email body.. there are two different query that two query output display in email body with line brake separately with heading.

Ed Wagner SSC Guru Points: 287024 More actions · Answer 14

SQL Galaxy (8/26/2016)
Pl. find the attached email which is receiving in Email body.. there are two different query that two query output display in email body with line brake separately with heading.

Close your first HTML table before starting the next one. From the looks of it, that's what you're missing.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions