June 29, 2004 at 10:01 am
There is a table on our database that holds HTML template data.
The actual HTML text is stord in a column that has the data type of "Text". When this column is viewed in EM it simply shows "<Long Text>". If you query the column in QA it only returns part of this text, the same happens regardless of whether the output results are in a grid, text, or output file (.rpt).
How can I view the entire contents of this column?
Thanks in advance,
Carl.
PS: @@TextSize is set to 64512
June 29, 2004 at 9:22 pm
Query Analyzer has by default a max limit on the column size at 255. If you go to Tools -> Options -> Results Tab you'll see a text box for Maximum characters per column. Unfortunately, the max value this can be set to is 8192.
If you want to get the entire text of the column, I don't think QA will be able to do it. You'll have to write a program that does this explicitly using the readtext, writetext functions in SQL or using the GetChunk methods in ADO, DAO, etc.
June 30, 2004 at 1:13 am
SQL Enterprise Manager and Query Analyser are both management tools. Not data reading / writing tools (though they do let you).
As rchawdry said, you'll need to use an external program to edit Text data types. I've found that if you use a Linked Table in Access, you will be able to view / edit the content in most cases.
How did you originally get the HTML data into SQL Server? How did you plan to view/maintain it?
Julian Kuiters
juliankuiters.id.au
June 30, 2004 at 2:24 am
DTS can do this for you in several ways. One way would be to write a simple query to select the data and then export it to a text file. You will then be able to view the entire contents of the text field.
Paul
June 30, 2004 at 2:56 am
Maybe try using substring function to view the text field in 256 chunks?!?
June 30, 2004 at 6:23 am
You can try running this code. The table name here is TXT. The column name is TXTCOL. Change those values as necessary. There are limitations, but at least you can see the entire contents of the text column in Query Analyzer.
/*
View large text column in Query analyzer
Notes:
- You will get a column header between each block of text that is printed
unless you uncheck Print Column Headings in Tools|Options, Results tab)
- The text breaks at fixed lengths, so words will be split across lines.
- Change the size (120) of @printStr and the value of @blocksize to a different value, if desired, to make it more readable.
*/
DECLARE @val varbinary(16), @textlen int
DECLARE @blocksize int, @bytesRemaining int, @offset int
DECLARE @printStr varchar(120)
SET @blocksize = 120 -- same size or smaller than @printStr
SET NOCOUNT ON
SELECT @val = TextPtr(txtCol)
FROM txt
WHERE id = 1
SET @textlen = (SELECT Datalength(txtCol) FROM txt WHERE id = 1)
SET @bytesRemaining = @textlen
SET @offset = 0
WHILE @bytesRemaining > 0
BEGIN
IF @bytesRemaining < @blocksize
SET @blocksize = @bytesRemaining
READTEXT txt.txtCol @val @offset @blocksize
SET @offset = @offset + @blocksize
SET @bytesRemaining = @bytesRemaining - @blocksize
END
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply