I'm glad I could help. That's nice code for running through the system tables. It's always great when we can put these computers to work to help solve problems.
>>> "Heffelfinger, Duane" <[email protected]> 4/2/2009 3:10 PM >>> Jim/Karen, Thanks for the suggestions. Jim’s statement SLEN hint gave me the hint I needed (See code below). The tables that were the culprit, I got a weird buffer error. -ERROR- Insufficient buffer space to process expressions. (2136) -ERROR- Expression not found (2141) Went to those tables and simply cleaned them up. SET FEEDBACK ON SET VAR + vc_Cmd TEXT = NULL, + vc_Tbl TEXT = NULL, + vc_Col TEXT = NULL, + vc_FleSze INTEGER = 200000, + vc_FleNme TEXT = 'temp.dat' DECLARE c1 CURSOR FOR + SELECT sys_table_name, sys_column_Name + FROM sys_columns T1, sys_tables T2 + WHERE T1.sys_table_id = T2.sys_table_id + AND sys_type_Name = 'VARCHAR' + AND sys_table_id NOT IN + (SELECT sys_table_id + FROM sys_views) OPEN c1 WHILE #PI <> 0 THEN FETCH c1 INTO + vc_Tbl INDICATOR vc_i, + vc_Col INDICATOR vc_1 IF SQLCODE <> 0 THEN BREAK ENDIF WRITE 'TABLE:', .vc_Tbl WRITE 'COLUMN:', .vc_Col SET VAR vc_Cmd = + ('SELECT (SLEN(' + .vc_Col + ')), * FROM' & .vc_Tbl & + 'WHERE (SLEN(' + .vc_Col + ')) >' & CTXT(.vc_FleSze)) &vc_Cmd ENDWHILE DROP CURSOR c1 CLEAR VAR vc_* RETURN Now to do an UNLOAD ALL and rebuild to see that all is well…… Thanks again, Duey From:[email protected] [mailto:[email protected]] On Behalf Of Jim Buddemeyer Sent: Wednesday, April 01, 2009 3:07 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Searching for large VARCHAR in database I don't know how many tables and VARCHAR fields are in the database but the following command might help locate the rows. Select UniqueIdentiferColumn (SLEN(varcharColumn)) from Table where (SLEN(varcharColumn)) > 2000 You can play with the threshold number to reduce the data set. I believe you need 7.6 and up to use SLEN with VARCHAR. Jim >>> "Heffelfinger, Duane" <[email protected]> 3/31/2009 3:28 PM >>> Good afternoon, Is there a way with R:Scope or a select trick to figure out what table is holding a large chunk of data in the .rb4 file? Even better can I figure out what table and row would be holding large VARCHARs values in the .rb4? Someone, somewhere has added several very large VARCHAR data chunks into the .rb4 and I would really like to know what table and row it’s attached to. Thanks for any ideas. Duey
