June 23, 2004 at 8:40 am
I have a column that is varchar (255) and need to change it to text. That's the easy part. The column is in a table that contains a sequence number that tracks the number of rows because the column must be concatanated together for display. The original database was version 6.5 that did not have a text datatype so the work around was to take the input and chop it into 255 lengths and use the sequence number to concatenate all the rows within the sequence in to a single paragraph. I now want to change the varchar 255 into a text field and delete all the code required to chop it into multiple row lengths and store a single row as text. I was using the following code but ran into a problem in that you can't declare a variable as text for the cursor.
DECLARE memo_cursor SCROLL CURSOR FOR
select Policy_Base,
Policy_Suffix,
Line_Number,
Comments
from #tmp_tbl
OPEN memo_cursor
DECLARE @Policy_Base varchar(14),
@Policy_Suffix int,
@Line_Number int,
@Comments varchar (250),
@entered_by int,
@entered_date datetime,
@tmp_comment varchar (255), --cannot use as local variable
@tmp_line_num int,
@tmp_base varchar(14),
@tmp_suffix int,
@status int
FETCH NEXT FROM memo_cursor INTO @Policy_Base,
@Policy_Suffix, @Line_Number,
@comments
WHILE (@@FETCH_STATUS = 0)
begin
if (isdate(left(@comments,19)) = 1) -- first line of sequence contains the date
begin
select @tmp_line_num = @Line_Number
end
else
begin
update #tmp_tbl set comments = comments + @comments where policy_base = @policy_base and policy_Suffix = @policy_suffix and line_number = @tmp_line_num
end
FETCH NEXT FROM memo_cursor INTO @Policy_Base,
@Policy_Suffix, @Line_Number,
@comments
end
close memo_cursor
deallocate memo_cursor
My thoughts are to save the text into a table and reference it, but not sure if that is the best course of action. Any advise is appreciated.
June 23, 2004 at 9:05 am
Hi,
Maybe I do not understand you correctly but…
Why not run true the whole cursor, built your @comments string and insert it after the cursor close?
Regards,
Leon Bakkers
June 23, 2004 at 9:43 am
The problem is that you can't declare a variable in win 2000 of datatype text: at least that's my understanding?
June 23, 2004 at 2:19 pm
I did find an answer after much searching:
update memo set comments = cast(comments as varchar (8000)) + @comments where policy_base
= @policy_base and policy_Suffix = @policy_suffix and line_number = @tmp_line_num
select @tmp_line_num
In essence I cast the column as a varchar(8000) and this allowed me to concatenate the 2 varchars and do a set into the comment column which is a text field. Thanks very much for your response to the question and after much research on the web, came to the understanding that this is a very troublesome datatype to work with in sql server.
June 24, 2004 at 7:52 am
AT ALL COSTS !!!! do not use TEXT !!!
1. It is extremely slow for large databases.
2. The text is stored seperately and referenced by a pointerle.
3. It prevents point in time recovery
4. Is not easily accessed using ODBC
Stick to using varchar(8000) or if you are storing whole documents store them in a subdirectory on your disk and store the path to the data in the tab
June 24, 2004 at 9:51 am
Thank you very much. I was not aware of all the limitations of using it. I certainly was aware of the difficulty of some of the problems of working with it in not having a variable text for script. There may be no way around it at this point, but I will make double sure we can live without it. Thanks again for your input.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply