I have the unfortunate task of dealing with an ntext column. I have to update
part of the contents but first I was just trying to display the contents in
Query Analyzer  using a script from page 61-62 of the Guru's transact sql
book. Well, the script does print out a few characters, skip a few, print a
few, skip a few, . . . It appears that accessing an ntext column is quite
different than accessing a text column. BOL is not very helpful on this.
Also, READTEXT only displays a few characters at a time no matter what the
chunk size is set to, so I can't tell if the problem is Query analyzer or
something else.  Does anyone have a source for useful info in dealing with
ntext?

thanks,
Michael

Snake wrote:
Show quoteHide quoteCan you post the code you are running. There shouldn't be a problem
reading the data from an ntext column.

I tried a test using the pubs.pub_info table which I recreated as
pub_info2 using an ntext column. QA does have a display setting for the
max number of character per column to display. Or it could be that there
are line breaks in the ntext that are not displaying correctly in the QA
grid. Try using text output and see if that helps.



create table dbo.pub_info2 (pub_id char(4) not null, logo image null,
pr_info ntext)
go

ALTER TABLE [dbo].[pub_info2] ADD CONSTRAINT [UPKCL_pubinfo2] PRIMARY
KEY  CLUSTERED
(
  [pub_id]
)  ON [PRIMARY]
GO
ALTER TABLE [dbo].[pub_info2] ADD  FOREIGN KEY
(
  [pub_id]
) REFERENCES [publishers] (
  [pub_id]
)
GO

insert into pub_info2 select * from pub_info
go

DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(pr_info)
   FROM pub_info2 pr INNER JOIN publishers p
      ON pr.pub_id = p.pub_id
      AND p.pub_name = 'New Moon Books'
select @ptrval
READTEXT pub_info2.pr_info @ptrval 0 25
GO

See http://www.aspfaq.com/2445 for some help on using UPDATETEXT.

I don't think you will need READTEXT to do what you want, see the following
(note though that it will create bogus carriage returns every 4000th
character, but existing control characters (CHAR(10,13,9 etc)) will still be
displayed correctly):




CREATE TABLE data
(
    id INT UNIQUE,
    txt TEXT
)
GO

SET NOCOUNT ON

DECLARE @foo NVARCHAR(4000)
SELECT @foo = REPLICATE('a',4000)

-- make one far > 4000 and a small one
EXEC('INSERT data(id,txt) SELECT 1,N'''+@foo+@foo+@foo+@foo+@foo+'''')
INSERT data(id,txt) SELECT 2,N'foobar'


DECLARE
@dLen INT,
@nRows INT,
@i INT,
@rowID INT,
@curLine NVARCHAR(4000)

SET @rowID = 1 -- change this to see the other result

SELECT
@i = 0,
@dLen = DATALENGTH(txt),
@nRows = (@dLen / 4000) + 1
FROM data WHERE id=@rowID

WHILE @i < @nRows
BEGIN
SELECT @curLine = SUBSTRING(txt, (4000*@i)+1, 4000) FROM data WHERE id =
@rowID
PRINT @curLine
SET @i = @i + 1
END

DROP TABLE data

case and order by problem?
Trouble with SP
How can I do this without a cursor??
sort by calculated value
table locking