Why is WRTIETEXT limit 2048 bytes

[faccorp]

Hello,

I have created two stored procedures. One of them uses WRITETEXT to write large of amounts of data to a 'text' field. The other uses 'READTEXT' to read the data.

Here is the write code:

CREATE PROCEDURE [update_EXTNOTES]

(@idnumber     [int],
 @Notes     [ntext])

AS

EXEC sp_dboption 'dbname', 'select into/bulkcopy', 'true'

DECLARE @ptrval binary(16)
SET TEXTSIZE 64000
SELECT @ptrval = TEXTPTR(EXTNOTES) 
FROM CUSTOMERS
WHERE [ID Number] = @idnumber

WRITETEXT CUSTOMERS.EXTNOTES @ptrval @Notes


EXEC sp_dboption 'dbname', 'select into/bulkcopy', 'false'
GO

And the READTEXT sp is

CREATE PROCEDURE [dbo].[get_EXTNOTES] 
(
    @idnumber     [int]
)

AS

DECLARE @val varbinary(16)
SET TEXTSIZE 64000
SELECT @val = textptr(EXTNOTES) FROM CUSTOMERS
WHERE [ID Number] = @idnumber
READTEXT CUSTOMERS.EXTNOTES @val 0 0
GO

I can't get this to store more than 2048 characters. Or maybe read more than 2048 characters. I'm not sure which it is. Something is limiting either the storage or reading to 2048 characters.

One thing I noticed, I am using VC 7.0 as my front end. When I use the Class manager for connection to these procedures, it creates a 'm_Notes TCHAR[8000];' variable for the text data for both the read and the write of the text. I guess this is normal, and I will need to bump up the 8000 to something greater eventually, however, why am I limited to 2048?

I saw something about DBTEXTLIMIT and DBTEXTSIZE. But how do I use the dbsetopt function? Where do I call it from?

SELECT @@TEXTSIZE return 64000

Thanks

 

[bboffin]

According to SQL Books online if you specify 0 as the number of bytes to be read in the READTEXT command it will default to 4096 (or 2048 Unicode characters)

Try changing this, the last 0 on the READTEXT line, to 64000 and see if it helps.

Alternatively why not simply select the EXTNOTES column as normal and let it be returned in the recrordset?

Bob Boffin

 

[faccorp]

bboffin,

Thanks. Sometimes I wonder that I can get anything done. I know I saw that in BOL but it didn't register.

This works. However, it brings up a new problem. If I specify READTEXT CUSTOMERS.EXTNOTES @val 0 64000 and there are not 64000 bytes to read, then I get an error. Somehow I must first figure out how many bytes there are to read and then make the size parameters a variable.

Thanks for your help.

 

[bboffin]

While I understand why you are using WRITETEXT for the UPDATE surely the get_EXTNOTES SP could be coded as:

.
.
SELECT ExtNotes FROM CUSTOMER WHERE [ID Number] = @idnumber
.
.

READTEXT is meant to be used to extract a substring from a text (or ntext) field. If all you want is the whole string a simple SELECT is all you need.





Bob Boffin

 

[faccorp]

Bob,

Thanks for the feedback. Yes I was originally using a basic SELECT statement but when I started to run into character limits and doing some research I ran into READTEXT and WRITETEXT. I have both of them working right now so I'll probably stick with it.

I changed the get_EXTNOTES to consider the length of the text to read

DECLARE @val varbinary(16)
DECLARE @len int
SET TEXTSIZE 64000
SELECT @len = DATALENGTH(EXTNOTES) FROM CUSTOMERS WHERE [ID Number] = @idnumber
SELECT @val = textptr(EXTNOTES) FROM CUSTOMERS
WHERE [ID Number] = @idnumber

READTEXT CUSTOMERS.EXTNOTES @val 0 @len