Arcpy Describe returns connected user as schema owner

1931
12
04-19-2019 01:27 PM
PhilLarkin1
Frequent Contributor

When returning the name of a Feature Class used to create a Query Layer, the schema name is incorrect. It matches the name of the user in the SDE connection file. 

In this example I'd expect to the see the name with the correct schema owner: dboExample. However I get userA.

If I create a Feature Layer with the same SDE connection the correct name is returned: assrsde.dboExample.vertical_units

This seems like a bug. Tested on 10.3 and Pro 2.3 arcpy.

input = "...\\userA_Staging_ASSRSDE.sde"

query = "SELECT pid_num as parcel, Shape FROM ASSRSDE.dboExample.Vertical_Units WHERE ISNULL(PID_NUM, '') <> ''"

parcel = arcpy.MakeQueryLayer_management(input_database=input,out_layer_name="memory\\VU",query=query)

print( arcpy.Describe(parcel).baseName.lower())

# returns: assrsde.userA.vertical_units‍‍‍‍‍‍‍‍‍
0 Kudos
12 Replies
PhilLarkin1
Frequent Contributor

The fully qualified name is the name given in the database. This would include the schema owner rather than the connected user schema. Given the scenario I've discussed at length this is what I would expect:

baseName: If I've aliased a table in the FROM clause it should return the alias, if I haven't and there is one table in the FROM clause it should return the full name [Database].[OwnerSchema].[Object], and if for some stupid reason im selecting a cartesian product there should be well documented logic to what name is going to be returned. Regardless, the name should include the owner schema. 

name: Returns the name I've given the Query Layer in the out_layer_name parameter.

I'd submit an Idea if I thought it would get enough votes to make a difference. At this point I'm assuming it won't get traction. 

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

As much as you may be right about the Idea not getting traction, there are lots of factors that motivate developers, and maybe being made aware of the issue might get one of them thinking about it.

How do databases name SQL queries from clients that don't persist as tables, views, etc... in the database?  If you created an SQL statement and saved it as a view in the database, it still wouldn't give you want you want because what is returned would be the schema containing the view and not what schemas are referenced in the SQL defining the view.

0 Kudos
PhilLarkin1
Frequent Contributor

I don't think there is a parallel in MSSQL. Results are passed to clients without generating a schema in the database. I couldn't pass a query and find some log parameter that would show a schema name for my connection.

0 Kudos