Problem with html table
-
Hi,
The table below has about 2 million rows.
I want to convert this to html table. I have non-english characters in the table & that is why I used nvarchar.
I used the following code to build html table.
Create Table Info([FullName] [nvarchar](500) NULL,
[TaxID] [varchar](30) NULL,
[RegNo] [varchar](30) NULL,
[StateRegNo] [varchar](30) NULL,
[Area] [nvarchar](200) NULL,
[BusinessType] [varchar](30) NULL,
[Explanation] [nvarchar](500) NULL,
[Address] [nvarchar](500) NULL,
[Telephone] [varchar](200) NULL,
[Fax] [varchar](200) NULL,
[InsertedDate] [datetime] NOT NULL,
[Link] [varchar](300) NOT NULL,
[Name] [nvarchar](500) NULL)
declare @tableHTML nVARCHAR(MAX)SET @tableHTML =
N'<table>' +
N'<tr><th>FullName</th>
<th>TaxID</th>
<th>RegNo</th>
......
<th>Name</th>
</tr>' +
CAST ( (
SELECT top 10000 td = CAST([FullName] AS nVARCHAR(500)),'',
td=TaxID ,'',
td= RegNo ,'',
......
td= CAST(Name as nvarchar(100))
FROM [dbo].[info]
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>'
The ran the following query and saved results in html file. But when I open, I see only 20 rows in the html table. Is there a restriction on how many rows/characters that can be converted to a table? IS there a better method of converting to html table?
-
seems there is no problem in query it is purely on management studio settings
change the variable type to xml, i think that will give you the entire result
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
Try casting all your string literals to NVARCHAR(MAX) BEFORE they are concatenated. The data type is likely being assumed to be NVARCHAR(4000) during concatenation because it is not specified.
Like this:
declare @tableHTML nVARCHAR(MAX)SET @tableHTML =
CAST(N'<table>' AS NVARCHAR(MAX)) +
CAST(N'<tr><th>FullName</th>
<th>TaxID</th>
<th>RegNo</th>
......
<th>Name</th>
</tr>' AS NVARCHAR(MAX)) +
CAST ( (SELECT top 10000 td = CAST([FullName] AS nVARCHAR(500)),'',
td=TaxID ,'',
td= RegNo ,'',
......
td= CAST(Name as nvarchar(100))
FROM [dbo].[info]
FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX) ) +
CAST(N'</table>' AS NVARCHAR(MAX))
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
I tried this. But I still get the same results.
-
I do think it is some setting that needs to be changed. I looked in Tools->options & changed the max number of characters displayed to 8192, Results to grid -> max characters displayed to unlimited. Query-Query options -> max number of characters displayed to 8192.
Is there any other place where settings can be changed?
-
When you say you saved to a file, if you used the SSMS file-destination of a query window you're probably subject to the settings regarding max text length that can be returned. Try using SSIS or BCP to get the data into a file.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
rash3554 (9/7/2014)
Hi,The table below has about 2 million rows.
I want to convert this to html table. I have non-english characters in the table & that is why I used nvarchar.
I used the following code to build html table.
Create Table Info([FullName] [nvarchar](500) NULL,
[TaxID] [varchar](30) NULL,
[RegNo] [varchar](30) NULL,
[StateRegNo] [varchar](30) NULL,
[Area] [nvarchar](200) NULL,
[BusinessType] [varchar](30) NULL,
[Explanation] [nvarchar](500) NULL,
[Address] [nvarchar](500) NULL,
[Telephone] [varchar](200) NULL,
[Fax] [varchar](200) NULL,
[InsertedDate] [datetime] NOT NULL,
[Link] [varchar](300) NOT NULL,
[Name] [nvarchar](500) NULL)
declare @tableHTML nVARCHAR(MAX)SET @tableHTML =
N'<table>' +
N'<tr><th>FullName</th>
<th>TaxID</th>
<th>RegNo</th>
......
<th>Name</th>
</tr>' +
CAST ( (
SELECT top 10000 td = CAST([FullName] AS nVARCHAR(500)),'',
td=TaxID ,'',
td= RegNo ,'',
......
td= CAST(Name as nvarchar(100))
FROM [dbo].[info]
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>'
The ran the following query and saved results in html file. But when I open, I see only 20 rows in the html table. Is there a restriction on how many rows/characters that can be converted to a table? IS there a better method of converting to html table?
HTML is usually associated with the presentation layer and, ostensibly, will be viewed by a human. I could be missing something here but why would we ever build a 2 million row result see formatted for human viewability?
--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) -
Business needs. Non-IT/non-database people need to see this and we thought if we can do 10,000 in each html file. they can scan through it and report us of any issues.
-
rash3554 (9/8/2014)
Business needs. Non-IT/non-database people need to see this and we thought if we can do 10,000 in each html file. they can scan through it and report us of any issues.If that's truly the reason, I recommend that you simply export the data as TAB separated files and let them open/analyze it in a spreadsheet where they can sort and filter to their heart's content.
--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)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply