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