October 21, 2004 at 10:13 am
I've recently found a problem moving data from one Table Text field to another Table Text field. I work for the Tennessee Legislature and we have Private and Public information. When the record in the Private table is approved the info is moved to a Public table. I have been using VB to write one field to the other but a large amount of text has failed with the Error: "The text is too long to be edited."
I thought this was an opportunity to use a stored procedure and created the following code:
Select @DescLength = DATALENGTH(DocMasterMemo.[Description])
from DocMasterMemo -- determines length of the textual data
where DocNumber = @BillNo
UPDATE publicdb..DocMasterMemo SET -- in Publicdb
[Description] = (Select SUBSTRING(Description, 1, @DescLength)
from DocMasterMemo -- uses SUBSTRING to overcome text field limitation
where DocNumber = @BillNo)
where Publicdb..DocMasterMemo.DocNumber = @BillNo
The variable @DescLength contains 76611 when the first statement is executed but only 7952 characters are moved after the second statement is executed.
Any ideas? Is there just some setting I need to change like the "Maximum characters per column:" setting in Query Analyzer
Bob
October 21, 2004 at 11:10 am
The SUBSTRING command implicitely converted Description into a varchar(8000). I'm guessing that the characters at positions 7953-8000 are spaces and so were implicitely RTRIMed.
The Query Analyzer setting only limits the amount of text it prints to the output window for a SELECT statement's output and does not impact the SQL executed at all (even for the SELECT).
If simply removing the SUBSTRING doesn't resolve your issues (based on the comment I guess not) try the following. I'm not sure if it will work or not.
UPDATE publicdb..DocMasterMemo SET -- in Publicdb
A.[Description] = B.Description
FROM publicdb..DocMasterMemo A
LEFT JOIN DocMasterMemo B ON B.DocNumber = @BillNo
where A.DocNumber = @BillNo
October 21, 2004 at 11:11 am
SUBSTRING is limited to the maximum string length in sql (8000 less any overhead)
why not just update one table directly from the other like this
UPDATE p
SET p.[Description] = d.[Description]
FROM publicdb..DocMasterMemo p
INNER JOIN DocMasterMemo d
ON d.DocNumber = p.DocNumber
WHERE p.DocNumber = @BillNo
or alternatively look up READTEXT and WRITETEXT on BOL
Far away is close at hand in the images of elsewhere.
Anon.
October 21, 2004 at 3:24 pm
Thank you Aaron & David; I'm expermenting with both suggestions. My boss also suggested that since the text was growing so large we could just display it as a PDF document and that is doable also.
Thanks Bob
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply