Queries failing on joined tables

261
4
11-02-2021 02:57 AM
Labels (3)
bogdanpalade2
New Contributor II

Good morning,

I am using ArcGIS Pro 2.8.3 to connect to an enterprise geodatabase (MS SQL server) where I only have read rights. In the geodatabase there are several tables and one point feature class containing some date fields. The joins between the feature class and the tables are working ok, but the queries not. Every query on any field from the feature class ends with the very informative message " There was an error with the expression". If I query on a field from on of the joined tables, it kind of works but it takes forever, like more than one hour to execute the query and sometimes just blocks. The feature class has under 10.000 points and the join tables have very few records. I cannot create an index on the join field because I only have read rights on the enterprise geodatabase and it fails every time I try. Maybe it's slower because of this.

Do you have any ideas how can I solve the queries problem?

 

0 Kudos
4 Replies
George_Thompson
Esri Frequent Contributor

You could try to have the DBA create a DB view of the join; https://pro.arcgis.com/en/pro-app/latest/tool-reference/data-management/create-database-view.htm. Then use that in ArcGIS pro to see how it works.

Be sure that you are given the correct permissions on the view to see it.

--- George T.
bogdanpalade2
New Contributor II

Hi George,

Thank you for your reply! I was interested in a solution that wouldn't need any modifications or write rights on the sql database itself. 

Is this the normal behavior when doing joins inside a enterprise gdb?

Regards,

Bogdan 

0 Kudos
George_Thompson
Esri Frequent Contributor

Hi Bogdan,

There are lots of factors that come into play with this. Many depend on where the join / query / etc. originates. I have seen it all over the years. This is why I recommended the DB view, since it would be persistent (unlike a join / query layer) for other users and not tied to a specific Pro project.

--- George T.
0 Kudos
bogdanpalade2
New Contributor II

Hi George,

Do you recommend doing it from ArcGIS Pro or from the SQL  server? How is the join done using the create database view tool in ArcGIS Pro, through the "View definition" field or somewhere else? 

0 Kudos