Can't get reference to result from MakeQueryLayer_management

[TimHaverland4]

When I try to run arcpy.MakeQueryLayer_management from the python command window, I can't get the resulting table added to the ArcMap display.

For example (I've changed the details to a generic case):

>>> arcpy.MakeQueryLayer_management('Database Connections\my_database.sde','a_table_view','select a_field from some_table','a_field')

The database I'm querying is Oracle 11g.

The command runs successfully (python returns <Result 'a_table_view'>, but when I go to the arctoolbox results tab, rt-click on the output, and choose Add to display I don't get anything. Likewise I can't seem to find this table through the various listing methods:

>>> mxd = arcpy.mapping.MapDocument("CURRENT") >>> df = mxd.activeDataFrame >>> arcpy.mapping.ListLayers(mxd,data_frame=df) [] >>> arcpy.mapping.ListTableViews(mxd,data_frame=df) [] >>> 

I've also tried getting a reference from:

arcpy.mapping.TableView('a_table_view')

and get an Assertion error.

Any ideas?

Thanks,

Tim

[TimHaverland4 · Accepted solution]

I FIGURED OUT WHY IT'S NOT WORKING!!!!

When running MakeQueryLayer_management from the python command window and your query only returns non-spatial columns, you must turn off the "Enable Background Processing" option:

1) From the Geoprocessing menu, choose Geoprocessing Options...
2) Under the "Background Processing" section, uncheck "Enable"

I'd consider this a bug ... if you have background processing enabled, and you run MakeQueryLayer_management from the python command window using a query that returns a spatial column, a layer is added to your map document. But, if your query does not return a spatial column, the resulting table does not get added to your map document and there's no way to add it through python or via the results window.

Thanks for everyone who helped me get to this point!:cool:

[OttarViken_Valvåg]

This problems seems to exist in 10.1 SP1 as well. I'm unable to work with Query Layers against sql server data when the query returns non-spatial data, if I'm using python scripts running outside ArcMap. My workaround is to add a dummy sql server geometry column in my sql server data so that my query layer becomes a feature layer.

[DamonPettitt]

I was having the same problem with not being able to get the resulting table from MakeQueryLayer when not using a spatial column.  I'm trying to work with this in a standalone python script so I'm not trying to add a table to ArcMap.  I just want to copy out the table as a standalone table in some file geodatabase i'm working with.

I stumbled upon another forum (Table analog of MakeQueryLayer) that made me realize

I still think there's a bug as the help for MakeQueryLayer in 10.2.2 says "If the SQL query does not return a spatial column, the output will be a stand-alone table."  which isn't true as it appears to return a pointer to a database (maybe similar to a sql view?).  I'll contact ESRI about this.

Here's how i got it working in my python code:

arcpy.MakeQueryLayer_management(database_connection,"out_layer_name", query,"FIELD1;FIELD2")

desc = arcpy.Describe("out_layer_name")

t = arcpy.mapping.TableView(desc.name)

arcpy.CopyRows_management(t, "myoutputtable")

[DamonPettitt]

This is a know bug and the bug # is NIM086004.

The workaround i listed where you could "describe" the layer and turn ultimately into a table seems like the best workaround in the meantime.

[MarkPeacey2]

It's great that a work around has been found for Python but Esri should really look at fixing this, especially as it's still an issue in 10.3.1. In Model Builder (thankfully we're not using it!) there's no way to use arcpy.Describe or mapping.TableView unless you add a python script to the model. There's another thread about this problem Create Query Layer Tool Output Object Not Found or Recognized  which came to much the same conclusion.

[DavidParsons1]

Folks is this bug back in 10.4.1 ? I appear to be getting it again .. Maybe ESRI never fixed it

[DamonPettitt]

I just checked the status of the bug # previously referenced and the status is "In Product Plan", so i guess that means it hasn't been addressed yet. :<(

[MattWilkie3]

Still present in 10.6, though it's fixed in ArcPro. I've summarized your and Tim Haverland‌ diagnosis and solution at arcmap - Make Query Layer - where are the results? - Geographic Information Systems Stack Exchange . Thank you for getting me on the right track!