READTEXT not returning all data

SELECT @.@.TEXTSIZE
SET TEXTSIZE 4096
SELECT @.@.TEXTSIZE
GO
DECLARE @.ptrval varbinary(16)
SELECT @.ptrval = TEXTPTR(emailTemplates.Data)
FROM emailTemplates WHERE [Name] = 'AccountInfoReceipt1'
READTEXT emailTemplates.Data @.ptrval 0 2000
GO

Thats my code. and it never returns the entire email template. why?

thx in advCurrently you are only reading the first 2000 characters. If you email template is longer than that, you will not get the entire template.|||heh. even when i change 2000 to 5000.....it still returns the same amount......i know all about the size and the offset. its just not working :/ :shrugs: dont owrry about it. i just used regular html files and read them into a string.

READTEXT Into @localvariable

Hi,
How do I get the results of a READTEXT call into a T-SQL local variable?
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.orgHi Daniel
"Daniel Jameson" wrote:

> Hi,
> How do I get the results of a READTEXT call into a T-SQL local variable?
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>
You can't declare a variable as text, therefore (assuming sql 2000) you are
limited to the maximum size of varchar. You can then chunk the text into
sections using substring e.g.
USE TEMPDB
CREATE TABLE MyTextTable ( TxtCol Text )
DECLARE @.ptrval binary(16)
DECLARE @.varchar varchar(8000)
SET @.varchar = REPLICATE('The quick brown fox jumped over the lazy dog', 200
)
INSERT INTO MyTextTable ( TxtCol ) VALUES ( @.varchar )
SELECT DATALENGTH(TxtCol) FROM MyTextTable
SELECT @.ptrval = TEXTPTR(TxtCol) FROM MyTextTable
SELECT LEN(@.varchar)
UPDATETEXT MyTextTable.TxtCol @.ptrval 7964 0 @.varchar
SELECT DATALENGTH(TxtCol) FROM MyTextTable
GO
DECLARE @.str varchar(8000)
DECLARE @.offset int
DECLARE @.maxlength int
SET @.offset = 1
SET @.maxlength = (SELECT DATALENGTH(TxtCol) FROM MyTextTable)
WHILE @.offset < @.maxlength
BEGIN
SELECT @.str = SUBSTRING(TxtCol, @.offset, 8000 ) FROM MyTextTable
SELECT @.str
SET @.offset = @.offset + 8000
END
DROP TABLE MyTextTable
John|||John,
This is what I needed:
SELECT @.str = SUBSTRING(TxtCol, @.offset, 8000 ) FROM MyTextTable
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4466E88C-ACD3-4C6B-88E0-20013BCD26BD@.microsoft.com...
> Hi Daniel
> "Daniel Jameson" wrote:
>
> You can't declare a variable as text, therefore (assuming sql 2000) you
> are
> limited to the maximum size of varchar. You can then chunk the text into
> sections using substring e.g.
> USE TEMPDB
> CREATE TABLE MyTextTable ( TxtCol Text )
> DECLARE @.ptrval binary(16)
> DECLARE @.varchar varchar(8000)
> SET @.varchar = REPLICATE('The quick brown fox jumped over the lazy dog',
> 200
> )
> INSERT INTO MyTextTable ( TxtCol ) VALUES ( @.varchar )
> SELECT DATALENGTH(TxtCol) FROM MyTextTable
> SELECT @.ptrval = TEXTPTR(TxtCol) FROM MyTextTable
> SELECT LEN(@.varchar)
> UPDATETEXT MyTextTable.TxtCol @.ptrval 7964 0 @.varchar
> SELECT DATALENGTH(TxtCol) FROM MyTextTable
> GO
> DECLARE @.str varchar(8000)
> DECLARE @.offset int
> DECLARE @.maxlength int
> SET @.offset = 1
> SET @.maxlength = (SELECT DATALENGTH(TxtCol) FROM MyTextTable)
> WHILE @.offset < @.maxlength
> BEGIN
> SELECT @.str = SUBSTRING(TxtCol, @.offset, 8000 ) FROM MyTextTable
> SELECT @.str
> SET @.offset = @.offset + 8000
> END
> DROP TABLE MyTextTable
> John

READTEXT Into @localvariable

Hi,
How do I get the results of a READTEXT call into a T-SQL local variable?
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.orgHi Daniel
"Daniel Jameson" wrote:
> Hi,
> How do I get the results of a READTEXT call into a T-SQL local variable?
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>
You can't declare a variable as text, therefore (assuming sql 2000) you are
limited to the maximum size of varchar. You can then chunk the text into
sections using substring e.g.
USE TEMPDB
CREATE TABLE MyTextTable ( TxtCol Text )
DECLARE @.ptrval binary(16)
DECLARE @.varchar varchar(8000)
SET @.varchar = REPLICATE('The quick brown fox jumped over the lazy dog', 200
)
INSERT INTO MyTextTable ( TxtCol ) VALUES ( @.varchar )
SELECT DATALENGTH(TxtCol) FROM MyTextTable
SELECT @.ptrval = TEXTPTR(TxtCol) FROM MyTextTable
SELECT LEN(@.varchar)
UPDATETEXT MyTextTable.TxtCol @.ptrval 7964 0 @.varchar
SELECT DATALENGTH(TxtCol) FROM MyTextTable
GO
DECLARE @.str varchar(8000)
DECLARE @.offset int
DECLARE @.maxlength int
SET @.offset = 1
SET @.maxlength = (SELECT DATALENGTH(TxtCol) FROM MyTextTable)
WHILE @.offset < @.maxlength
BEGIN
SELECT @.str = SUBSTRING(TxtCol, @.offset, 8000 ) FROM MyTextTable
SELECT @.str
SET @.offset = @.offset + 8000
END
DROP TABLE MyTextTable
John|||John,
This is what I needed:
SELECT @.str = SUBSTRING(TxtCol, @.offset, 8000 ) FROM MyTextTable
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4466E88C-ACD3-4C6B-88E0-20013BCD26BD@.microsoft.com...
> Hi Daniel
> "Daniel Jameson" wrote:
>> Hi,
>> How do I get the results of a READTEXT call into a T-SQL local variable?
>> --
>> Thank you,
>> Daniel Jameson
>> SQL Server DBA
>> Children's Oncology Group
>> www.childrensoncologygroup.org
> You can't declare a variable as text, therefore (assuming sql 2000) you
> are
> limited to the maximum size of varchar. You can then chunk the text into
> sections using substring e.g.
> USE TEMPDB
> CREATE TABLE MyTextTable ( TxtCol Text )
> DECLARE @.ptrval binary(16)
> DECLARE @.varchar varchar(8000)
> SET @.varchar = REPLICATE('The quick brown fox jumped over the lazy dog',
> 200
> )
> INSERT INTO MyTextTable ( TxtCol ) VALUES ( @.varchar )
> SELECT DATALENGTH(TxtCol) FROM MyTextTable
> SELECT @.ptrval = TEXTPTR(TxtCol) FROM MyTextTable
> SELECT LEN(@.varchar)
> UPDATETEXT MyTextTable.TxtCol @.ptrval 7964 0 @.varchar
> SELECT DATALENGTH(TxtCol) FROM MyTextTable
> GO
> DECLARE @.str varchar(8000)
> DECLARE @.offset int
> DECLARE @.maxlength int
> SET @.offset = 1
> SET @.maxlength = (SELECT DATALENGTH(TxtCol) FROM MyTextTable)
> WHILE @.offset < @.maxlength
> BEGIN
> SELECT @.str = SUBSTRING(TxtCol, @.offset, 8000 ) FROM MyTextTable
> SELECT @.str
> SET @.offset = @.offset + 8000
> END
> DROP TABLE MyTextTable
> John

READTEXT error

I can't figure out I continually get a msg 7124 error using READTEXT.
The script:
DECLARE @.TextfieldPtr varbinary(16)
DECLARE @.CommentsBytes int
SELECT @.TextfieldPtr = TEXTPTR(TextComments)
FROM Comments
WHERE CommentId = 25
SELECT @.CommentsBytes = DATALENGTH(TextComments)
FROM Comments
WHERE CommentId = 25
/*This returns a value of 17,830*/
SET TEXTSIZE @.CommentsBytes
READTEXT Comments.TextComments @.TextfieldPtr 0 @.CommentsBytes
The result:
**********
17830
Server: Msg 7124, Level 16, State 1, Line 19
The offset and length specified in the READTEXT statement is greater
than the actual data length of 8915.
***********
Why 8915, which is half of the actual size returned by the DATALENGTH()
function?If it is NVARCHAR, use DATALENGTH(column)/2
On 3/12/05 4:31 PM, in article
1110663061.251893.218680@.z14g2000cwz.googlegroups.com, "Rlane"
<rmathuln@.pacbell.net> wrote:

> I can't figure out I continually get a msg 7124 error using READTEXT.
> The script:
> DECLARE @.TextfieldPtr varbinary(16)
> DECLARE @.CommentsBytes int
> SELECT @.TextfieldPtr = TEXTPTR(TextComments)
> FROM Comments
> WHERE CommentId = 25
> SELECT @.CommentsBytes = DATALENGTH(TextComments)
> FROM Comments
> WHERE CommentId = 25
> /*This returns a value of 17,830*/
> SET TEXTSIZE @.CommentsBytes
> READTEXT Comments.TextComments @.TextfieldPtr 0 @.CommentsBytes
> The result:
> **********
> 17830
> Server: Msg 7124, Level 16, State 1, Line 19
> The offset and length specified in the READTEXT statement is greater
> than the actual data length of 8915.
> ***********
> Why 8915, which is half of the actual size returned by the DATALENGTH()
> function?
>

READTEXT and return values

First question :

How can I set the return value of READTEXT to a variable of type nvarchar.

Second question :

I have a table t1 with a ntext column n1.

The ntext column has has words separated by empty space.
Each word can be assumed to be of size <= 255 characters.

How can I extract all the keywords in the ntext column to a table t2 with a column word nvarchar(255).

Assume that the text in column n1 is big enough so that it cannot be cast into a nvarchar or any other simpler type.

Any help on this is greatly appreciated.
Please do provide a sample code.

Alok.Have you referred to books online for READTEXT topoic.