Create Query Layer Tool Output Object Not Found or Recognized

KevinMorris · ‎09-05-2013

ArcGIS 10.1 SP1. ArcSDE 10.1, SQL Server 2008 R2

I have been trying to use the Make Query Layer Tool to create a table view from SDE tables (_VM) in SQL Server 2008 R2 and save the table by a number of export methods with limited success. I have been unsuccessful in saving the information through either the tools, modelbuilder, python window or arcpy (external python). Only manual export is working.

The tool "make query layer" ran from within ArcMap will create the proper table from the SDE/SQL Server 2008R2 query and tables, however, it is not possible to export this Table_View layer using export Table to Table or CopyRows or Table to Geodatabase as shown in the help literature. The ONLY method to export the information has been in the Table of Contents (TOC), through export from the table.

Delving a little deeper I tried to get a little infomration on this object and it appears to contain part of the SQL/SDE connection string as part of it's object as several tools have indicated a .name of "SEKI_HazardTrees.DBO.%HazTree" when the original given name is HazTree only (SEKI_HazardTrees is the name of the SQL/SDE database). I also receive this name when I run arcpy.describe on the object and return a path of the sde connection string.

desc.name = SEKI_HazardTrees.DBO.%HazTree
desc.dataType = TableView
desc.nameString = HazTree
desc.path = Z:\Projects\HazardTrees\inpsekigistest_HazardTrees.sde ( the path of the SQL database)

The error received is very general and similar between failing tools:

Failed to convert SEKI_HazardTrees.DBO.%HazTree. ERROR 999999: Error executing function.
Failed to execute (CopyRows).

or

Cannot open HazTree (the Table View - in arcpy)

I have tried to override the tool from including the SEKI_HazardTrees to it's name by setting up the scratch and normal workspaces to no avail.

IF you have a working python .py example of this (not from the ESRI site that does not work) please post. I have tried all the versions posted by ESRI.

Thanks
Kevin

I'm including my code here for what its worth, but since the stand-alone tools couldn't work I wouldn't expect the arcpy gp tools to work any better (although sometimes they do).

python code

#Import system modules
import arcpy

arcpy.env.overwriteOutput = True

# Set data path also tried removing the r and change / to //
workspace = r"Z:/Projects/HazardTrees/Results.gdb"
intable = r"Z:/Projects/HazardTrees/Results.gdb/Veg_HazardTreeSurvey_tbl"

inpsekigistest_HazardTrees_sde = r"Z:/Projects/HazardTrees/inpsekigistest_HazardTrees.sde"

arcpy.env.workspace = workspace
arcpy.env.scratch = workspace

# Create the Query Layer and return the Table-View name for verification
test = arcpy.MakeQueryLayer_management(inpsekigistest_HazardTrees_sde, "HazTree", "SELECT * from SEKI_HazardTrees.dbo.Veg_HazardTreeSurvey_tbl_VW", "OBJECTID", "", "", "").getOutput(0)
# Get the properties of the Query Layer if possible
objMQ = arcpy.Describe(test)
print objMQ.name
print objMQ.dataType
print objMQ.nameString
print objMQ.path
print test

# Get column count - note that if the MakequeryLayer function fails, then an empty table with only the schema will be produced.
# However, we have tested this and copied the SQL query and parameters from ArcMap tools for which we were successful generating the Table View so this should be ok

#print int(arcpy.GetCount_management(test).getOutput(0))   # doesn't work
#print int(arcpy.GetCount_management("HazTree").getOutput(0)) # doesn't work

#arcpy.CopyRows_management(test, r"Z:/Projects/HazardTrees/hz002.dbf")  # doesn't work Error 99999
#arcpy.CopyRows_management(test, "Z://Projects//HazardTrees//hz002.dbf")  # doesn't work Error 99999

BakaryKoné · ‎03-12-2014

I'm having exactly the same problem here.

It looks like the result of the Query layer Tool cannot be used by other subsequent tools in model builder or python, unless you make it manually.
I was trying to use the Generate XY Events tool with the result of the Make Query Layer tool, but all i could get was error 00023 (Canot access to the event table properties)
I tried any possible tools to convert the Query layer into another type of structure (table, view, etc.) and also tried to save it in whatever type of file, with absolutely no succes.

The same things happens in Model Builder and Python. But manually in ArcMap, it works great.

Please, do someone has a workaround for this ?

Bakary.

muhammadalmas · ‎03-12-2014

Facing similar issue..

MattGray · ‎03-14-2014

I am also facing a similar problem. Trying to use arcpy.makequerylayer on table with lat/long fields but no shape field.

LeonardRugiel · ‎06-17-2014

python code

#Import system modules
import arcpy

arcpy.env.overwriteOutput = True

# Set data path also tried removing the r and change / to //
workspace = r"Z:/Projects/HazardTrees/Results.gdb"
intable = r"Z:/Projects/HazardTrees/Results.gdb/Veg_HazardTreeSurvey_tbl"

inpsekigistest_HazardTrees_sde = r"Z:/Projects/HazardTrees/inpsekigistest_HazardTrees.sde"

arcpy.env.workspace = workspace
arcpy.env.scratch = workspace

# Create the Query Layer and return the Table-View name for verification
test = arcpy.MakeQueryLayer_management(inpsekigistest_HazardTrees_sde, "HazTree", "SELECT * from SEKI_HazardTrees.dbo.Veg_HazardTreeSurvey_tbl_VW", "OBJECTID", "", "", "").getOutput(0)
# Get the properties of the Query Layer if possible
objMQ = arcpy.Describe(test)
print objMQ.name
print objMQ.dataType
print objMQ.nameString
print objMQ.path
print test

t = arcpy.mapping.TableView(test[0])
arcpy.TableToTable_conversion(t, workspace, "hz002")


# Get column count - note that if the MakequeryLayer function fails, then an empty table with only the schema will be produced.
# However, we have tested this and copied the SQL query and parameters from ArcMap tools for which we were successful generating the Table View so this should be ok

#print int(arcpy.GetCount_management(test).getOutput(0))   # doesn't work
#print int(arcpy.GetCount_management("HazTree").getOutput(0)) # doesn't work

#arcpy.CopyRows_management(test, r"Z:/Projects/HazardTrees/hz002.dbf")  # doesn't work Error 99999
#arcpy.CopyRows_management(test, "Z://Projects//HazardTrees//hz002.dbf")  # doesn't work Error 99999

Add the following to your script after you create your query layer.

t = arcpy.mapping.TableView(test[0])
arcpy.TableToTable_conversion(t, workspace, "hz002")

If you want it to save as a dbf, you can replace the TableToTable with:
arcpy.TableToDBASE_conversion(t, r"Z:/Projects/HazardTrees")

See TableView for more details.

StuartPersyn · ‎11-06-2015

Had the same issue. This works, thank you Leonard Rugiel so much! I couldn't do anything with the table view coming from the Make Query Layer tool until now.

MichaelVolz · ‎04-15-2016

Will this code also work if you need to create a featurelayer from the query layer instead of a tableview?

DanNarsavage · ‎05-04-2016

Not sure whether this addresses your problem but I found it (and then this thread) during my search for solving another issue. Based on it, you may want to check that background geoprocessing is turned off.

Can't get reference to result from MakeQueryLayer_management