Can't open ArcCatalog Database Connection SQL database Tables with field names greater than 31 characters

Question

I am using SQLEXPRESS as a test environment.

I have a database connection in ArcCatalog to a SQL database. Some tables in the SQL database have field names greater than 31 characters and these tables will not open in ArcCatalog.

Can the greater than 31 character SQL Field names be given aliases using Server Management studio and then re-connected to ArcCatalog or do the field names in the SQL database simply need to be shortened (which will be tough as it is owned by a 3rd party)?

Answer 1

The 31 character limit is an artifact of multi-RDBMS support in ArcSDE (Oracle limits table and column names to 30 characters).

These are some of the relevant transfer buffer limits from the 10.2.0 include file:

#define SE_MAX_COLUMN_LEN 32 /* MAXIMUM COLUMN NAME LENGTH */
#define SE_MAX_TABLE_LEN 160 /* MAXIMUM TABLE NAME LENGTH */
#define SE_MAX_SCHEMA_TABLE_LEN 30 /* MAXIMUN TABLE 'ONLY' NAME LENGTH */
#define SE_MAX_ALIAS_LEN 32 /* MAXIMUM TABLE ALIAS LENGTH */
#define SE_MAX_ENTITY_LEN 256 /* MAXIMUM ENTITY TYPE LENGTH */
#define SE_MAX_HINT_LEN 1024/* MAXIMUM DBMS HINT LENGTH */
#define SE_MAX_SCHEMA_LEN 32 /* MAXIMUN SCHEMA NAME LENGTH */
#define SE_MAX_OWNER_LEN 32 /* MAXIMUM TABLE OWNER NAME LENGTH */
#define SE_MAX_INDEX_LEN 160 /* MAXIMUM INDEX NAME LENGTH */
#define SE_MAX_GROUP_LEN 128 /* MAXIMUM GROUP NAME LENGTH */

These are buffer sizes, which include a terminator, so the actual character limits are one less.

I'm afraid you need to change your tables.

Answer 2

Your easiest option would be to create a View of the table and access that through ArcCatalog. If you're not familiar with them a View is a representation of tables that is defined with a SQL statement. In your SQL statement you can change the name of fields as follows:

SELECT ReallyReallyReallyLongFieldName AS FieldOne FROM Table1

There are many other advantages to using Views including better security since you are not giving people direct access to the underlying tables.

Another thing you may want to reconsider is reviewing the design of your database. In most cases you should probably not be using field names that are that long.