TSQL Insert into table fails

[skalyanaraman]

Hi,
We are using Version 3 of Texis on NT (release March 15 2001). Here is what we are trying to do.

1. We have a table tbldoc with 45 fields in it.
2. We create a table tbltemp with the same set of 45 fields.
3. We do,
Insert into tbltemp select <Field_list> from tbldoc

where the <Field_list> is all the fields in tbldoc in the same order as in tbltemp.
This produces the error, "000 SQL failed". WHY??
4. If I remove the last field from the tbltemp creation and do the same in the select for the insert, it works.

Is there a limit for the number of fields. Does not look like. Because we already have the table tbldoc with those 45 fields.

Why does it work if the last field is removed?
By the way we use TSQL to do all this work.

Please help!! Thanks!!

[mark]

Do you get any other messages before "SQL failed"? Are the types of all of the fields the same? What's the type of the last field that you are removing?

[skalyanaraman]

Hi,
No. No error messages before that. The datatype of the last field is varchar and of size 25500000. Infact there are a lot of fields are varchar and of this size. But the data in them are not that long.
Anyway some more information that could be useful.
The tbldoc was created in a older version of TSQL (version 3 from August 1999). The tbltemp is created using the new version March 2001. We are copying the tbldoc.tbl and doing a addtable and then the creation of tbltemp and the insert(which fails..).

Thanks!!

[mark]

The tables should be fully compatible from old to new versions.
Try letting texis create the table for you.

"create table tbltemp as select * from tbldoc"

Also, if you're addtable'ing it why copy it to another identical table?

BTW, you don't need to declare such huge varchar fields. A varchar field is allowed to exceed it declared size with no performance penalties. The size for varchar's is simply a hint used internally.

[bart]

My 2 Cents:

You should <b>NEVER</b> declare a field as varchar(25500000). Especially since you have a bunch of them. Texis will try to pre-allocate REAL RAM to fit the sum total of a single record. Just declare it to be the size of the avg real field data that it will encounter. Texis will then allocate RAM based on need if it actually encounters a record that big.