SQL Server 2014 compatibility with ArcGis

2904
8
10-01-2015 05:17 AM
Highlighted
New Contributor II

Hi All,

We store our goedatabase into SQL server 2014. I created a DB connection to our DB from ARCMap, but I have some issues with naming, our tables names have dot "." example: student.courses. I have an  issue with this, as it says, 3 levels of accessibility is not supported. Does it mean  that I have to change the our tables names.

Second problem, when I used a test table to create a shape from, I couldn't it keeps showing this error:

---------------------------

Create Feature Class

---------------------------

There was an error trying to process this table.

Invalid SQL syntax[[Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'key'.]

---------------------------

OK  

---------------------------

Is there any thoughts on these errors I get. I am new ARCGis, I have just installed it.

Bests, -Hanan

8 Replies
Highlighted
Esri Frequent Contributor

Hi Hanan,

Can you please provide a screenshot of the table in ArcCatalog or SQL Server Management Studio? This will clarify the issue. Also what version of ArcGIS Desktop are you using and what version is the geodatabase in?

But it looks like you are trying to create a feature class name with a "." in it? If so that is not supported.

In SQL Server the normal naming convention is something like this:

DatabaseName.DataOwner.TableName = ESRI.GIS.Polygons

Adding an extra "." in the name is probably the issue.

Here is a link to some of the limits and naming conventions within ArcGIS.

Enterprise geodatabase size and name limits—ArcGIS Help | ArcGIS for Desktop

-George

GeodatabaseManaging DataEnterprise GIS

--- George T.
Reply
0 Kudos
Highlighted
New Contributor II

My ArcGIS version is 10.3 advanced licensed copy. For the Database snap, all of our tables are like this:

dbo.student.course.category

dbo.student.course

Like above, this is the name convnsion we follow. I don't know why '.' is not supported in the table's names. I think to create views but also, we are gonna have dbo.; the database owner as a prefix to any table we are gonna use.

Do you suggest any workarounds?

Reply
0 Kudos
Highlighted
Esri Frequent Contributor

I do not know of a work around. When you add the ".course" to the name of the feature class that is where it seems to not work.

I tested and tried to create a feature class with the name "student.course" and got an error during creation.

-George

--- George T.
Reply
0 Kudos
Highlighted
New Contributor II

Actually, I changed the name of one table to test, and it worked. But it throws an exception the column name exceeds 31 as shown below:

---------------------------

Edit Properties Failed

---------------------------

Failed to edit the selected object(s).

Invalid column definition[Column name 'famous_public_building_loop_count' exceeds 31 characters long.]

---------------------------

OK  

---------------------------

This is a disaster for me to change the whole DB table names and columns, it means going through all tables and columns, our database is quiet huge.

Reply
0 Kudos
Highlighted
Esri Frequent Contributor

There are limitations that ArcGIS has with enterprise geodatabases and length of the table name is one of them....

Do you have the geodatabase repository tables in this database or where all the table created outside of ArcGIS?

--- George T.
Reply
0 Kudos
Highlighted
New Contributor II

That is frustrating. I guess I will try to create views in SQL server, and connect them to ArcMap. I will try as we cannot afford changing the whole DB.

I installed ArcGIS today, so it is empty, what we have is stored in SQL server.

Reply
0 Kudos
Highlighted
MVP Esteemed Contributor

ArcGIS isn't the only software that doesn't like dots/periods in SQL Server object names.  In fact, certain Excel tools will error out as well.  Although Microsoft doesn't provide any specific guidance against using dots in SQL Server object names, I think you would find a vast majority of SQL Server DBAs, and even some developers, would say it is not a good practice to do so since SQL Server uses dots for very specific reasons in its 4-part object naming convention.

Highlighted
New Contributor II

That is frustrating knowing that we can still have workarounds, but I doubt it is gonna work as well. Trials will tell.

Reply
0 Kudos