topic Cannot use Select By Attributes or Def Query with Join in Versioned SDE in ArcGIS Enterprise Questions

Cannot use Select By Attributes or Def Query with Join in Versioned SDE

WillJames — Wed, 03 Dec 2014 21:16:50 GMT

I am working in a company that uses a versioned SDE database to manage our work flows and we have run into the following problem, when a user, who is not the administrator of the SDE database, creates a feature class it gets tagged with that users ID in the SDE database.

E.G.

USA."CompanyName\user".USA_Counties as apposed to the administrator created file which looks like USA.DBO.USA_Counties

This isn't a huge issue except when you attempt to join the attribute table of this feature class to another table and then query it using either select by attributes or a definition query because instead of using field aliases arc populates the GUI with full field names that include the "" and \ which precipitates a error when you try to write a query.

E.G.

USA."CompanyName\user".USA_Counties.County_Name = 'Greene'

result

"There was a problem selecting

An invalid SQL statement was used."

Does anyone know of a work around so that our users can create their own content and still use basic functionality or is the only solution that the DBO administrator has to create all of the content for our entire company?

Thanks,

Wllm1313

Product Version 10.2.2.3552

Re: Cannot use Select By Attributes or Def Query with Join in Versioned SDE

Anonymous User — Fri, 19 Dec 2014 17:13:36 GMT

1) The query syntax issue is likely caused by the special character which separates the domain and the user's name.

2) The solution to this issue is to use a named database user for creating data. We typically do not allow users to create new tables in the primary production SDE geodatabase. There is a "super user" who uses the schema account (database.CARTO.Landmarks) to create a new table when needed, and the other users (via OS auth) modify data within those existing tables.

Re: Cannot use Select By Attributes or Def Query with Join in Versioned SDE

WillJames — Fri, 19 Dec 2014 18:45:53 GMT

Marianne,

I appreciate your response but it doesn't answer my question;

"Does anyone know of a work around so that our users can create their own content and still use basic functionality or is the only solution that the DBO administrator has to create all of the content for our entire company"

We currently use the "solution" your propose but this is cumbersome as we have 30+ users in one database and having each person request the admin to create tables/featureclasses wastes a lot of time.

Thanks,

Will

Re: Cannot use Select By Attributes or Def Query with Join in Versioned SDE

Anonymous User — Fri, 19 Dec 2014 18:54:47 GMT

The other solution I offer is using a named database user, i.e., each user who needs to create a table must use a database user instead of OS authentication. That would remove the special character which I think is creating your query issue.

I can't reproduce this issue--what database platform are you using? From your description, I assumed SQL Server...

Re: Cannot use Select By Attributes or Def Query with Join in Versioned SDE

Anonymous User — Fri, 19 Dec 2014 21:12:03 GMT

I just had another thought: is it possible that the SDE administrator, in an attempt to track user's tables, has created a Default Schema for each user?

If you have SQL Server Management Studio, check a user's properties for the default schema. The value you see there is the middle value of the three-tier naming convention in SQL Server.