Error building key for index "name". (Error 2199)
This error is generated when index key truncation is about to occur, typically during index creation or modification such as with the INDEX command. This can happen with use of a key that contains an expression involving a Memo field, whose length in not fixed, such as in the following example:
INDEX ON charfld1 + memofld1 TAG mytag
An index file with truncated index keys is a source for potentially incorrect data results. Visual FoxPro does not allow for key truncation during index creation or modification. When index key truncation is about to occur, this error is reported. If you desire key truncation, you should adjust the index expression to explicitly truncate the key.
Due to issues associated with key truncation, the SQL engine (such as during a SQL SELECT command or View creation) might fail to build a temporary index to optimize a join evaluation if it is unable to accurately determine the maximum size of the key. This might cause the join to be evaluated as a Cartesian product and as a result could impact performance. To allow you to diagnose this issue, a message is included in the SQL ShowPlan, which is controlled by the SYS(3054) function. When this function value is greater than or equal to 11, the message "Error building temporary index" is included in the ShowPlan to indicate the key truncation problem. You can fix this problem by adjusting join conditions to make the key size more explicit and to allow the temporary index to be built successfully.
Occasionally, the SQL engine will build a temporary composite index (this is a special index created internally by the product and is not supported through the INDEX command). In this case, some parts of the index may cause key truncation, while others might not. During the first attempt to build the index, those parts which cause problems are identified. The offensive parts are then removed from the key and the index is built again without them. The SQL ShowPlan will indicate this problem, but it is still possible that the join can still be optimized using valid parts only.
See Also
Concepts
Join Conditions for Tables, Queries, and Views