January 9, 2012 at 2:36 pm
Is there a quick and dirty way to format varchar(max) SQL Server output into HTML without using Reporting Services (which is not installed at our site)?
Initially, this article (http://technet.microsoft.com/en-us/library/ff730936.aspx) about PowerShell seemed promising. The example uses PowerShell "get-service," and I was thinking it might be possible to swap out the "get-service" output with the output I generated from SQLCMD.EXE, but I didn't have any luck there. Here is the code I was trying:
$a = "<style>"
$a = $a + "BODY{background-color:peachpuff;}"
$a = $a + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"
$a = $a + "TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:thistle}"
$a = $a + "TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:PaleGoldenrod}"
$a = $a + "</style>"
# The following formats "get-service" output correctly
Get-Service | Select-Object Status, Name, DisplayName |
ConvertTo-HTML -head $a -body "<H2>Service Information</H2>" |
Out-File temp.htm
start temp.htm
# The following does not format SQLCMD.EXE output correctly
sqlcmd -S server_name\instance_name -E -Q "select application_cde, env_cde, migration_note from metrics.dbo.application_migration_note" | select-object application_cde, env_cde, migration_note |
ConvertTo-HTML -head $a -body "<H2>SQL Server Query Output</H2>" |
Out-File temp.htm
start temp.htm
However, since one of the columns is varchar(max), SQLCMD.EXE may not be the solution. It looks as if SQLCMD.EXE truncates the output in a similar fashion as a SQL Server Management Studio query window.
Any ideas? I would like to output the entire varchar(max) data into HTML without having to install any additional software.
January 11, 2012 at 5:23 am
Still looking for some ideas... 🙂
January 12, 2012 at 6:27 am
This is crude, but seemingly effective...
Save the following code as convert_sql_xml_to_html.ps1. Be aware that the pasted code contains tab characters to tidy up the HTML.
# See "SQL Server 2008 R2 Unleashed," page 1865 ff
<###############################################################
#
# Create a test table, populate it with data, and extract xml
#
###############################################################>
$HTML_File_NME = "temp.htm"
$SQLCommand_STR = "
if db_name() <> 'tempdb' use tempdb;
set nocount on
declare
@Body_STRvarchar(max)
;
create table junk
(
fnamevarchar(20),
lnamevarchar(20)
);
insert into junk values ('John', 'Doe');
insert into junk values ('Jane', 'Doe');
insert into junk values ('Abby', 'Smith');
print '<HTML>
<HEAD>
<style>''td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ''</style>
</HEAD>
<BODY>
<TABLE CELLPADDING=1 CELLSPACING=1 BORDER=1>
<TR BGCOLOR=#FFEFD8>
<TH ALIGN=CENTER>First Name</TH>
<TH ALIGN=CENTER>Last Name</TH>
</TR>';
:XML ON
select @Body_STR =
(
select
fname,
lname
from
junk
order by
lname,
fname
for xml raw ('TR'), elements
);
:XML OFF
select @Body_STR = replace(@Body_STR, '<TR><fname>', '<TR>
<TD>')
select @Body_STR = replace(@Body_STR, '</fname>', '</TD>')
select @Body_STR = replace(@Body_STR, '<lname>', '
<TD>')
select @Body_STR = replace(@Body_STR, '</lname></TR>', '</TD>
</TR>
')
print @Body_STR;
print '</TABLE>
</BODY>
</HTML>';
drop table junk;
" # End of $SQLCommand_STR variable
sqlcmd.exe -S server_name\instance_name -E -w1000 -b -l 32 -d tempdb -Q "$SQLCommand_STR" -o $HTML_File_NME
<###############################################################
#
# Display XML file information
#
###############################################################>
write-output ""
write-output "Displaying the contents of $HTML_File_NME`:"
write-output ""
cat $HTML_File_NME
start $HTML_File_NME
exit
You can execute the saved script in a PowerShell console window with the following. (Your PowerShell execution policy should be set to at least RemoteSigned.)
.\convert_sql_xml_to_html.ps1
January 12, 2012 at 6:40 am
I've been using For XML to put SQL query results into HTML pretty successfully for a few years.
Looks basically like this:
IF OBJECT_ID(N'tempdb..#T') IS NOT NULL
DROP TABLE #T ;
CREATE TABLE #T
(ID INT IDENTITY
PRIMARY KEY,
ColA VARCHAR(10)) ;
INSERT INTO #T
(ColA)
VALUES ('Hello'),
('there') ;
SELECT (SELECT ID AS TD,
'',
ColA AS TD,
''
FROM #T AS T2
WHERE T2.ID = #T.ID
FOR
XML PATH(''),
TYPE) AS TR
FROM #T
FOR XML PATH('table') ;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 12, 2012 at 7:00 am
Thanks for the tip. That gets rid of some replace statements.
January 12, 2012 at 7:18 am
GSquared,
XML is not my strong suit...
When I tidy up the output from your sample query, I am seeing multiple table tags, which can cause the data to misalign. Is there a way to generate a single table tag set, instead of a table tag for each row?
<table>
<TR>
<TD>1</TD>
<TD>Hello</TD>
</TR>
</table> <== Can this tag be omitted?
<table> <== Can this tag be omitted?
<TR>
<TD>2</TD>
<TD>there</TD>
</TR>
</table>
January 12, 2012 at 7:34 am
Sorry, needs to be nested on level deeper.
SELECT (
SELECT (SELECT ID AS TD,
'',
ColA AS TD,
''
FROM #T AS T2
WHERE T2.ID = #T.ID
FOR
XML PATH(''),
TYPE) AS TR
FROM #T
FOR XML PATH(''), TYPE)
FOR XML PATH('table');
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 12, 2012 at 7:42 am
Thanks, that works better. 🙂
January 12, 2012 at 8:40 am
GSquared,
One more question...
I started down the path of using XML because I read a Google hit that said XML would retrieve the entire column (presumably even for a varchar(max) column). However, when I test further with what I have learned about XML, it appears that my varchar(max) column is getting truncated to ~7400 bytes during XML retrieval. The original insert was for ~9800 bytes on that column.
Any ideas?
January 12, 2012 at 8:47 am
When you say it's truncating, is that because of something you're seeing in SSMS, or because of something in a file output or something like that?
To be clear, is it a display error because of SSMS, or is it happening somewhere else?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 12, 2012 at 8:57 am
I am running with SQLCMD.EXE under PowerShell because of the column truncation that normally happens in SQL Server Management Studio.
However, SSMS and SQLCMD.EXE appear to be truncating the data at the same place (7728 bytes). Not sure why.
January 12, 2012 at 9:00 am
The data appears to be 9741 bytes long in the database, according to "select len(column_name)..."
January 12, 2012 at 9:04 am
I'm not familiar enough with SQLCMD to say much on that.
Maybe someone more familiar with that can help better. I'll ask around and see if I can find someone to help on that part of it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 12, 2012 at 9:43 am
Thanks for the feedback. SQLCMD.EXE and SSMS are truncating the data at the same place (~7700 bytes) in the output. Both tools report the data as being 9741 bytes long.
January 12, 2012 at 11:00 am
Hmmm... It looks as if this truncation is a limitation of the print command. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=95740
The data appears to be intact, and I can access it in 8000 byte chunks.
print '### First chunk ###';
print substring(@Body_STR, 1, 8000);
print '### Second chunk ###';
print substring(@Body_STR, 8001, 16000 - @Length_CNT);
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply