SQL Server Field Name Length Too Long

2191
5
12-20-2016 09:21 AM
Highlighted
New Contributor III

I  am connecting to a SQL Server database that has field names greater than 31 characters.

Pretty much any geoprocessing tool fails on a SQL Server table that has field names this long as it is an ArcMap limitation.

Anyone know a command that will DUPLICATE the table without causing an error? Possibly by copying and then altering the field name. ALSO I need to use model builder so I can't manipulate/export in ArcMap first.

Reply
0 Kudos
5 Replies
Highlighted
Regular Contributor

One possibility is to have your DBA (or database developer) create a view on the table with shortened field names where needed.  We've had to do this with table names and fields that are reserved words in ArcGIS.  For example, there was a table named "Transaction" in a straight SQL database.  If I recall, ArcCatalog couldn't even see the table.

Highlighted
New Contributor III

Thanks for the tip, that maybe the solution. Yeah ArcCatalog kind of crashes when clicking on the table. 

Reply
0 Kudos
Highlighted
Esri Contributor

Two simple workarounds that you could try:
1. Create a query layer and use and alias for the field name:

select reallyreallyreallylongfieldname as shorterfieldname from table

Creating a query layer—Help | ArcGIS for Desktop 

2. Create a view in the DBMS and consume the view in ArcMap:

Create view table_vw as

select reallyreallyreallylongfieldname as shorterfieldname from table

Highlighted
New Contributor III

I will try the query layer and see if that works!

Reply
0 Kudos
Highlighted
New Contributor III

It doesn't work in this case because the table doesn't have a unique row identifier and the geoprocessing command is not letting me create one from within the SQL window SELECT statement.  I will persue some other avenue - thanks again!

Reply
0 Kudos