topic DBMS error - 42s02 invalid object name invalid for use as a key in an index in Geodatabase Questions

DBMS error - 42s02 invalid object name invalid for use as a key in an index

NicolasRoux — Mon, 17 Feb 2020 09:20:08 GMT

Hi everyone !

I try to copy GDB file content (no creator on that source) on SDE GBE. The GDB File is build with relations table. I use "drag and drop" action to copy the files.

The copy was interrupted due to one file (table file) that the type is invalid for uses as a key colomn in an index (abstract of the error message 😉 ).

This file is set up with a key column built like 1111111-1/1 (varchar) and wide as 8000 characters.

I think that :

1- the key column is not right.

2- The field is too wide for the transaction.

Am I right or not ?

Thanks for reading and answering !

Nicolas

Re: DBMS error - 42s02 invalid object name invalid for use as a key in an index

WilliamCraft — Mon, 17 Feb 2020 15:31:06 GMT

If the field is a VARCHAR, then you can have up to 8000 characters. Check to see if the column definition uses VARCHAR(MAX). If it does, try using a limited size rather than MAX. What version of SQL Server are you using? There is a limitation in SQL Server (depending on the version) that VARCHAR(MAX) and NVARCHAR(MAX) cannot be used in indices. Instead, a fixed length must be used to make it eligible for use in the index.

Re: DBMS error - 42s02 invalid object name invalid for use as a key in an index

NicolasRoux — Mon, 17 Feb 2020 15:59:57 GMT

Hi William,

The column definition uses VARCHAR(MAX), up to 8000 characters. The SQL Server version is 2016 (don't SP version as well).

Re: DBMS error - 42s02 invalid object name invalid for use as a key in an index

WilliamCraft — Mon, 17 Feb 2020 16:03:51 GMT

Change the column definition from VARCHAR(MAX) to something else with a suitable length such as VARCHAR(100). Let me know if it works after that.

Re: DBMS error - 42s02 invalid object name invalid for use as a key in an index

NicolasRoux — Tue, 18 Feb 2020 06:31:06 GMT

After the change, it didn't work. I get the same error message.

Re: DBMS error - 42s02 invalid object name invalid for use as a key in an index

WilliamCraft — Tue, 18 Feb 2020 13:42:16 GMT

After making the change, did you close and re-open ArcGIS Desktop?

Re: DBMS error - 42s02 invalid object name invalid for use as a key in an index

NicolasRoux — Thu, 27 Feb 2020 12:34:40 GMT

Hi,

Sorry for my late response... Yes, I did it.

And I asked to the table owner changing the length field.

Thanks for your answers 😉