Using textread but document still truncated

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Using textread but document still truncated

Author

Topic

kitty024
Starting Member

3 Posts

Posted - 2003-11-05 : 12:11:14

I've got a document which is about 40,000 characters stored in an ntext field. I have used 'set @@textsize' in query analyser to increase my textsize to 64000.

I have written a query :

DECLARE @Val varbinary(16)
SELECT @Val = TEXTPTR([DocumentXML])
FROM [tblDocument]
WHERE [DocumentID] = '91'
READTEXT [tblDocument].[DocumentXML] @Val 0 40000

but when i run it, it only brings back 32,000 characters.

Can anybody help/explain what i'm doing wrong?

Kitty

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-05 : 14:05:14

QA can't display in its result pane more than 32kB of data.

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-05 : 16:00:10

I don't that's it...it doesn't say in BOL but I always thought there was a 32k limit...maybe it's a buffer...

Why not just read in chucks of 32k?

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-05 : 16:02:09

Do this...check out:

SELECT @@TEXTSIZE

BOL Says it supercedes READTEXT....

Brett

8-)

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-06 : 01:55:23

quote:
QA can't display in its result pane more than 32kB of data.

Sorry for this my oops. In fact I don't know the upper limit for the subject.
Anyway 40000 characters can be returned / displayed without any problems.
Of course if only "Maximum characters per column" option is set to value >= 40000.

kitty024
Starting Member

3 Posts

Posted - 2003-11-06 : 06:10:27

I'm trying to output to a web page using Coldfusion, i realize that you can't view more than a certain number of characters using QA. I only used QA to increase the @@textsize, but it doesn't seem to have made any difference.

I tried increasing the size used within READTEXT. eg:

DECLARE @Val varbinary(16)
SELECT @Val = TEXTPTR([DocumentXML])
FROM [tblDocument]
WHERE [DocumentID] = '91'
READTEXT [tblDocument].[DocumentXML] @Val 0 42000

The maximum it will work with is 41353 but even at that size it is truncating my document (it's bringing back about 2/3 of the document). If i increase it above that it comes back with the following error message:

Macromedia][SQLServer JDBC Driver][SQLServer]The offset and length specified in the READTEXT statement is greater than the actual data length of 41353.

I know the whole document is there because if i link the sql database to an Access Project i can view the document in the Access field.

Do I need to change another setting elsewhere, I've read something about DBTextsize in the DB-Library, but i don't know where the DB-Library is or how to find it. In case it helps i'm using SQL Server 2000.

Any ideas/thoughts would be gratefully appreciated.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-06 : 06:46:46

SQLServer JDBC Driver

Check its docs. Seems it is the truncator.

kitty024
Starting Member

3 Posts

Posted - 2003-11-06 : 09:45:48

Thanks for your reply Stoad, it got me thinking and searching, seems there are problems with the SQL Sever JDBC driver but in the end it wasn't that.

I had to increase the text buffer in Coldfusion Administrator. It was set at 64,000 chars but i've upped it to 100,000 and that seems to have fixed the problem.

Big thank you to everybody who replied.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources