sql server mseq: UPDATETEXT Not Working -- sql server mseq

Re: UPDATETEXT Not Working

WilliamG
12/24/2003 11:08:42 AM

Thank you Vishal,

I did as you suggested and adapted your example to my
database tables. I was sucessful in appending TEXT data
from a source table's cell to a destination table's cell
on one specific row.

I need to append the TEXT data from the source table's
cells to the destination table's cells on all of the rows.
The corresponding rows from the source table to the
destination has a 1-to-many relationship (repectively) and
can be specified with a PK to FK WHERE statement. Can
this be done?

I have pasted a copy of the query I tried below:

USE ctelsql
GO

BEGIN TRAN
DECLARE @ptrval1 binary(16)
DECLARE @ptrval2 binary(16)
-- Assign a handle for source data column to @ptrval1
SELECT @ptrval1 = TEXTPTR(ReferenceNotes)
FROM Org, BGPerOrg1
WHERE (Org.Org_ID = BGPerOrg1.Org_ID)
AND (BGPerOrg1.Notes IS NOT NULL)
-- Assign a handle for destination data column to @ptrval2
SELECT @ptrval2 = TEXTPTR(Notes)
FROM BGPerOrg1, Org
WHERE (Org.Org_ID = BGPerOrg1.Org_ID)
AND (BGPerOrg1.Notes IS NOT NULL)
-- Append a carrage return and linefeed to the destination
deleting no data
UPDATETEXT BGPerOrg1.Notes @ptrval2 NULL 0 '
'
-- Append the source data to the destination data deleting
no data
UPDATETEXT BGPerOrg1.Notes @ptrval2 NULL 0
Org.ReferenceNotes @ptrval1
COMMIT

[quoted text, click to view]

Re: UPDATETEXT Not Working

Vishal Parkar
12/24/2003 7:24:38 PM

Hi WiliamG,

See following example on the pubs database which does the similar thing what you are
looking for, implement the same logic in your script.

use pubs
go

-- Following text column will be the source that will get appended to second query's row
DECLARE @ptrval1 binary(16)
DECLARE @ptrval2 binary(16)
Declare @x int
SELECT @ptrval1 = TEXTPTR(pr_info)
FROM pub_info pr
where pr.pub_id = '0736'

-- Following row's text will get appended by the above source row text.
SELECT @ptrval2 = TEXTPTR(pr_info), @x=datalength(pr_info)
FROM pub_info pr
where pr.pub_id = '1389'
UPDATETEXT pub_info.pr_info @ptrval2 @x 0 pub_info.pr_info @ptrval1

-- Vishal

Re: UPDATETEXT Not Working

Vishal Parkar
12/26/2003 7:04:16 PM

Hi,

See following 2nd example, Im considering 2 tables in pubs database
1)publishers
2)pub_info

publishers table is acting as a primary key table. Im changing "pub_name" column's
datatype to
TEXT to have similar table structure as that of you.

pub_info table is acting as a foreign key table. On which im declaring a cursor to update
respective records.

Ex:
use pubs
go
-- altering column of publishers to TEXT.
alter table publishers alter column pub_name text

-- insert one additional row into pub_info table by dropping constraint.
-- assume following table as foreign key table

alter table pub_info drop constraint UPKCL_pubinfo

alter table pub_info add constraint FKCL_pubinfo foreign key(pub_id)
references publishers(pub_id)

-- insert one additional row into pub_info table
insert into pub_info select * from pub_info where pub_id ='0736'

declare @pub_id varchar(10),
@ptrval1 binary(16),
@ptrval2 binary(16),
@x int
set @pub_id = 0
while @pub_id is not null
begin
-- following is the loop for primary key table
select @pub_id=min(pub_id), @ptrval1 = min(TEXTPTR(pub_name)) from publishers
where pub_id > @pub_id

-- the cursor to loop through foreign key table.

declare c1 cursor for
SELECT TEXTPTR(pr_info), datalength(pr_info)
FROM pub_info pr
where pr.pub_id = @pub_id

open c1

fetch c1 into @ptrval2, @x

while @@fetch_status = 0
begin

-- updating foreign key table using following updatetext statement.

UPDATETEXT pub_info.pr_info @ptrval2 null 0 publishers.pub_name @ptrval1

fetch c1 into @ptrval2, @x
end
close c1
deallocate c1

end

HTH
-- Vishal

Re: UPDATETEXT Not Working

WilliamG
12/30/2003 10:33:29 AM

Hi Vishal,

I ALTERed the publishers and pub_info tables as you
outlined and INSERTed one additional row in the pub_info
table.

I entered the statements you provided and encountered this
error in the DECLARE CURSOR statement:

Server: Msg 8654, Level 16, State 1, Line 16
A cursor plan could not be generated for the given
statement because it contains textptr ( inrow lob ).

I'm not sure what "lob" means, do you know?

Thank you again for supporting me.

[quoted text, click to view]

Re: UPDATETEXT Not Working

WilliamG
12/31/2003 11:13:21 AM

Hi Vishal,

I did what you suggested and it worked!
Thank you.

There were a few issues I had some difficulty with:
I looked at the data using EM and QA.
EM would show "<Long Text>" in some of the
pub_info.pr_info fields (preventing me from seeing the
contents) and show the first line of text in the rest (I
could click in the cell and view all rows). I would
include a screen shot of the results if I could attach it
to this post.
QA returned the first 256 characters of the
pub_info.pr_info fields.
After using both EM and QA I was unable to confirm that
the data was appended to the fields showing "<Long Text>"
in EM. Since all of the other fields show the appended
text, and there were no errors when I ran the query, I
assume the data was appended in all rows.

I am now going to apply what you have shown me to my
database. I am still left wondering why EM shows "<Long
Text>" in some fields and the first row of information in
other fields. Do you know?

Thank you again, I appreciate your help

WilliamG

[quoted text, click to view]

Re: UPDATETEXT Not Working

Vishal Parkar
12/31/2003 9:23:00 PM

hi WilliamG,

That sound strange to me. but i didn't get that error. However try following
approach that does not uses CURSOR and use the WHILE loop instead. But to make
this loop iterated through each row im adding one more identity column to
pub_info table. Basically LOB is a "large object" datatype which stores large
character data (exceeding varchar limit) in seperate pages.

use pubs
go
-- altering column of publishers to TEXT.
alter table publishers alter column pub_name text

-- insert one additional row into pub_info table by dropping constraint.
-- assume following table as foreign key table

alter table pub_info drop constraint UPKCL_pubinfo

alter table pub_info add constraint FKCL_pubinfo foreign key(pub_id)
references publishers(pub_id)

-- insert one additional row into pub_info table
insert into pub_info select * from pub_info where pub_id ='0736'

-- add an identity column to pub_info table. (addition to the previous post)

alter table pub_info add idd int identity

declare @pub_id varchar(10),
@idd int,
@ptrval1 binary(16),
@ptrval2 binary(16),
@x int
set @pub_id = 0

while @pub_id is not null
begin

-- following is the loop for primary key table
select @pub_id=min(pub_id) from publishers
where pub_id > @pub_id

select @ptrval1 = TEXTPTR(pub_name) from publishers where pub_id = @pub_id

-- instead of cursor im using another while loop

set @idd = 1

while @idd is not null
begin

SELECT @ptrval2=TEXTPTR(pr_info), @x=datalength(pr_info)
FROM pub_info pr
where pr.pub_id = @pub_id and idd = @idd

-- updating foreign key table using following updatetext statement.
If @@rowcount > 0
UPDATETEXT pub_info.pr_info @ptrval2 @x 0 publishers.pub_name @ptrval1

select @idd=min(idd)
from pub_info
where idd > @idd and pub_id = @pub_id

end

end

HTH

-- Vishal

P.S. before executing this script on real data i would suggest take a backup
of existing table using INSERT INTO...SELECT command as a old copy of existing
table.

Re: UPDATETEXT Not Working

Vishal Parkar
1/5/2004 7:15:33 AM

Hi WilliamG,

Viewing TEXT data.

Through SQL Server "query analyzer" the maximum number of characters that can
be returned is 8192. This setting can be done by going to

Tools | Options | choose "Results" tab. Check the maximum character per
column. if it is not 8192 then set it to 8192

If your data is crossing this limit then you will have to make use of
substring function as a workaround.
First make sure your datalength is going beyond 8192 characters by typing
following command.
Ex:
SELECT DataLength(TextColumn) FROM Text_table.

Above query will return you the lengh of the text column for each row of the
table.Now to view this data you can make use of SUBSTRING function as follows.

Select substring(TextColumn, 1, 8000), substring(TextColumn, 8001, 8000)
,substring(TextColumn, 16001, 8000) from Text_Table

OR another alternative is :

use DTS/bcp to transfer the TEXT data to the flat file and view it.

-Vishal

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server mseq group.