readtext still truncating document

[kitty024]

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.

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

 

[mkal]

Couple of things that come to mind.

1. The data type varbinary() is a variable-length binary data type, that can take lengths from 0 to 8000 bytes.

Your @val is set to varbinary(16) try increasing it.

2. READTEXT takes table.column|text_ptr|offset|size as arguments. Try increasing the value of the size argument, yours is 40000.

size

Is the number of bytes (when using the text or image data types) or characters (when using the ntext data type) of data to read. If size is 0, 4 KB bytes of data are read.

 

[kitty024]

Thanks for your reply mkal.

1. I checked out books online and it says under READTEXT that the varbinary must be set to 16. I tried increasing it anyway but it didn't make any difference.

2. I tried increasing the size used with READTEXT. 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.

 

[kitty024]

Finally sorted the problem:

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.