At 08:01 AM 7/03/2006, you wrote:

>I just backed up and restored a 1.5 DB.
>
>On the restore, using DBWorkbench, I get an error "sort record size
>of 65640 bytes is too big".

AFAIK, this error occurs when the size of an output record (which
might be in an intermediate set) in a GROUP BY or ORDER BY statement
is too wide.

>This happens at the end of recreating some FK's, possibly on the
>first procedure to be recreated.

Procedures aren't recreated during a restore.

>I saw a post in this ng, however it was related to a very, very
>large select which involved 30 + tables.

You might be referring to the size limit of a query plan (48 Kb). If
so, that's a different problem to yours. Your problem relates to a
record size, which is a max. of 64Kb, less a few bytes.

Search your metadata for a constraint - most likely a check
constraint - that has an ordered or grouped subquery expression.

>The only thing I can think of is a UDF that I recently changed and
>use in several procedures. The udf is for escaping HTML code
>produced within a procedure, and is declared like this:
>
>DECLARE EXTERNAL FUNCTION F_ENCODE_HTML
> CSTRING(32767)
> RETURNS CSTRING(32767) FREE_IT
> ENTRY_POINT 'Str_EncodeHTML' MODULE_NAME 'Primavera_UDF';
>
>I might call this function several times in a select statement like:
>Select F_Encode_HTML( Name ), F_Encode_HTML( City ) ... from <table>

That could do it. Your restore is falling over during constraint
building, so it would likely be a call that's located in an ordered
or grouped subquery expression in a check constraint.

FWIW, that UDF declaration is going to blow up whenever you use it if
you are passing those strings to or from varchars. You should reduce
those cstring size declarations to 32765, the byte-length limit of
varchar. But there's a worse flaw there. The input string size is
way too large, since passing a full size string into it and then
adding escape characters is going to cause an overflow in the result
string, as sure as night follows day.

Next time you write a UDF, it would be rather a good idea to test it
using extremes for your arguments before you plug it into a database
you care about.

>Could this be the problem

Yes.

> and is there any way to restore from this backup?

Probably not. I hope you weren't trying to restore it using the
-r[eplace_database] switch. That's No. 1 in the Top Six Ways to Kill
a Database.

If you still have an intact database, it would be good to put it
offline and make a filecopy of it to put on ice before you proceed further.

Inspect your metadata closely for these pitfalls and eliminate them
before trying another backup.

And, when you restore it, restore to a different filename using the use the
-c[reate_database] option.

./heLen