4

I am using the latest ArcGIS Pro 2.2.1 and trying to use a geospatial database that is hosted in an Azure SQL Server. The issue is that ArcGIS Pro fails to query databases that have "-" in their name.

I tried with a test database and a spatial table that I called, for example, test-geo-dataset. I could successfully connect to the database server, select this database and see the different tables from the catalog panel. However I am unable to move data to the map. It also fails exporting data to a local geodatabase with the following error:

ERROR 999999: Something unexpected caused the tool to fail. Please refer to the error help for potential solutions, or contact Esri Technical Support http://esriurl.com/support. Underlying DBMS error[[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '-'.] Failed to execute (TableToTable).

If I rename the same database to, for example, test_geo_dataset, then I can drag tables to the map and export to a local geodatabase and all this works flawlessly.

I tried finding more information regarding this limitation but I cannot find if it is an ArcGIS Pro or SQL Server issue. All other SQL clients that I am using are capable of querying the test-geo-dataset (with "-") database. Is there a fix or a workaround for this issue?

  • No, this is not possible. ArcGIS requires valid SQL standard identifiers. If double-quotes are needed to access either table names or column names, ArcGIS woll not be able to access the data. – Vince Sep 6 '18 at 12:43
  • I believe this is a limitation of geodatabases across the board. Same limitation exists in ArcMap. – Jay Cummins Sep 6 '18 at 12:43
  • 3
    Some databases allow special characters such as forward slashes (/), underscores (_), dollar signs ($), dashes (-), dots (.), or mixed cases. ... However, ArcGIS does not delimit object names. Do not create any tables, feature classes, indexes, databases, users*, roles, or other object names that require delimiters if you will be using them with ArcGIS. The object will be created in the database, but you cannot access it from ArcGIS. goo.gl/PjPKm4 – Jay Cummins Sep 6 '18 at 12:45
  • Perhaps ArcGIS Pro does not use delimited names when it makes the query docs.microsoft.com/en-us/sql/odbc/microsoft/…. Without delimiters the the query means "[test] minus [geo] minus [dataset]". – user30184 Sep 6 '18 at 12:46
  • Jay, please make a full answer of this documentation quote. – Vince Sep 6 '18 at 14:22
5

Esri states this as a limitation for geodatabases on their Enterprise geodatabase size and name limits page.

Character type limits in object names
Database management systems have different definitions of acceptable characters for object names. Most names must begin with a letter and cannot contain spaces, backslashes, or reserved database management system keywords. Some databases allow special characters such as forward slashes (/), underscores (_), dollar signs ($), dashes (-), dots (.), or mixed cases. Sometimes the database allows you to use special characters or reserved keywords, or force mixed, upper-, or lowercase names if you provide the object name enclosed in delimiters, such as double quotation marks.

However, ArcGIS does not delimit object names. Do not create any tables, feature classes, indexes, databases, users*, roles, or other object names that require delimiters if you will be using them with ArcGIS. The object will be created in the database, but you cannot access it from ArcGIS.

I've repeatedly used mixed-case delimited by double-quote in PostgreSQL only to find out it doesn't work for ArcGIS Query Layers. Either have to re-work the object naming in the database, or create views that aliases everything down to lower case.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.