Failure of Query Layer in Model Builder - Error 000229
Error on creating a Query Layer in a geoprocessing Model or Python Script
ArcGIS 10.4.1 (Citrix)
Python 2.7
[SQL Server Native Client 11.0] - on Citrix
I have a series of Query Layers that I can create manually no problem and join back to a feature layer, but when I try to perform the process in model builder or a python script I get: ERROR 000229: Cannot open <query layer>. The query I'm testing is really simple, so I'm pretty sure the error isn't with the query, but I have several more complex queries to process. AddJoin fails because it can't locate the query layer, and it really doesn't matter what tool you use next to process the query layer (Table to Table, Append,...) they all fail with the same error. I can add the query layer to the display, it is fine, but model builder/python can't locate for the iteration of the next tool.
Referencing this article and a number of Geonet posts, I tried to disable the 64-bit background geoprocessing to no avail.
• The data is inaccessible, unavailable, or invalid.
• There are unsupported data types in 64-bit background processing.
• Unsupported tools are unable to run in background processing.
• The 64-bit background geoprocessing does not have the necessary 64-bit libraries.
I recall in another lifetime having an error like this when using the wrong SQL Client at SQL Server 2014. Seems at SQL Server 2012 Microsoft announced the depreciation of the SQL Server Native Client (OLE DB) and at that point ESRI (confusingly) recommended moving to the ODBC client.
MS Data Access Technologies Road Map
Better Practice: Connect to SQL Server from ArcGIS Using MS ODBC (overview of ESRI confusing recommendations for SQL Client)
I've also seen issues with the 64-bit libraries vs 32-bit installed on the client.
I'm not convinced either of these possibilities are the issue, but I'm curious, has anyone else seen this error or can reproduce it at 10.4.1? Unfortunately, I don't have a stand-alone ArcGIS install to test against.
I did find a work around using database views.
Thanks,
Susan