basiclife
asked on
MSSQL 2000 TEXT Field type
I'm trying to retrieve around 14000 characters from a TEXT field in MSSQL.
I've set the datatype to TEXT, placed the field last in the SELECT query. The data is being truncated at 8k (In VB6, ASP AND Query analyser). Drivers are the latest I can find.
I then came across this:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
DECLARE @iChunkSize int,
@iLoop int,
@iStart int
SET @iChunkSize = 500
SET @iLoop = 2
SET @iStart = 0
BEGIN TRAN
DECLARE @ptr varbinary(16)
SELECT @ptr = textptr(Contents)
FROM intranet_brief
WHERE BriefID = 1
SET @iStart = @iChunkSize * @iLoop
READTEXT Intranet_Brief.Contents @ptr @iStart @iChunkSize
COMMIT TRAN
GO
Which is a slight variation on the one in Books Online however, there's no wasy of knowing when you've hit the end of the text as you can't LEN() a text. Therefore, however much I loop, I end up erroring when I try and read past the end of the data.
Please excuse the hurried write-up, many other things to do.
Any help greatly appreciated
(Oh, Version: MS SQL Server 2000 - Not changeable)
I've set the datatype to TEXT, placed the field last in the SELECT query. The data is being truncated at 8k (In VB6, ASP AND Query analyser). Drivers are the latest I can find.
I then came across this:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
DECLARE @iChunkSize int,
@iLoop int,
@iStart int
SET @iChunkSize = 500
SET @iLoop = 2
SET @iStart = 0
BEGIN TRAN
DECLARE @ptr varbinary(16)
SELECT @ptr = textptr(Contents)
FROM intranet_brief
WHERE BriefID = 1
SET @iStart = @iChunkSize * @iLoop
READTEXT Intranet_Brief.Contents @ptr @iStart @iChunkSize
COMMIT TRAN
GO
Which is a slight variation on the one in Books Online however, there's no wasy of knowing when you've hit the end of the text as you can't LEN() a text. Therefore, however much I loop, I end up erroring when I try and read past the end of the data.
Please excuse the hurried write-up, many other things to do.
Any help greatly appreciated
(Oh, Version: MS SQL Server 2000 - Not changeable)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The problewm with that is: you can't substring a TEXT field
Not true. Query Analyzer will allow you to substring a TEXT field (in the same manner as I indicated) provided the substring you bring back does not exceed the 8000 char limit.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Incidentally, 8000 characters is a SQL Query Analyzer limitation. There is nothing to prevent you from returning the whole text column to an ASP page or VB6 using ADO.
ASKER
Excuse the error - no longer @ work to test it.
The original Q is due to the vact that ASP would only bting back 8K, I tried in Vb in case of an ASP problem and same result. I know query analyser truncates anyway but thought if I whacked the return length over 8K, it would work - WRONG :)
Will give this a crack tomorrow morning, thanks.I can't just substring to get 2 fields as the 1400 was approx, may well be over 16K - ie 3 fields. Am using a WYSWIG Rtf control with RTF->HTML converter built in to allow users to post briefs to an intranet. the problem is, some moron decided to paste from a word doc - hence word formatting - about 6 x longer than it has to be *sigh*
I'll probably end up looping around and sub-stringing.
Thanks for your help. As I said before, will check tomorrow then award (or come back with more probs)
The original Q is due to the vact that ASP would only bting back 8K, I tried in Vb in case of an ASP problem and same result. I know query analyser truncates anyway but thought if I whacked the return length over 8K, it would work - WRONG :)
Will give this a crack tomorrow morning, thanks.I can't just substring to get 2 fields as the 1400 was approx, may well be over 16K - ie 3 fields. Am using a WYSWIG Rtf control with RTF->HTML converter built in to allow users to post briefs to an intranet. the problem is, some moron decided to paste from a word doc - hence word formatting - about 6 x longer than it has to be *sigh*
I'll probably end up looping around and sub-stringing.
Thanks for your help. As I said before, will check tomorrow then award (or come back with more probs)
>>I tried in Vb in case of an ASP problem and same result.<<
Than you are not doing something write. Post your code.
Than you are not doing something write. Post your code.
And it would help if I could spell, let alone write :). Obviously that should have read:
Than you are not doing something right.
Than you are not doing something right.
ASKER
Ok, so i've been VERY VERY stupid. I tried substring and it would onyl return the first 8k chars. I tried len() and it failed so I couldn't tell the length. The error on the cursor indicated a data length of 8k Chars.
After due consideration, I double-checked the sp that created the record and spotted that the data type was VARCHAR(8000) not text - the data was being truncated before the insert. This explains why Vb/ASP/etc.... were giving incorrect responses.
Anyway, thanks very much for you help guys. will split opints evenly as it wasn't either solution that solved it but you both helped. Thanks!
After due consideration, I double-checked the sp that created the record and spotted that the data type was VARCHAR(8000) not text - the data was being truncated before the insert. This explains why Vb/ASP/etc.... were giving incorrect responses.
Anyway, thanks very much for you help guys. will split opints evenly as it wasn't either solution that solved it but you both helped. Thanks!
SET @Chunk2 = SUBSTRING(Contents, 7001, 7000)