topic Why would MakeQueryLayer_management return an empty object? in ArcGIS Pro Questions

Why would MakeQueryLayer_management return an empty object?

JesseTemplin2 — Fri, 10 Sep 2021 00:40:59 GMT

Hello,

I am using MakeQueryLayer_management to read from a Postgres database and populate a table in a file geodatabase.

I have two sde connections for the postgres database. Let's call them user_A.sde and user_B.sde. Each corresponds to a user on the postgres database with the same permissions. Both users can query the table in a third party tool. And both connections can view the table in ArcGIS Pro. And both connections can drag and drop the table into an ArcGIS Pro project.

The problem is when I call MakeQueryLayer_management in ArcPy, one of the users returns the table as expected and the other returns an empty result object. The only change I am making is the path to the sde connection file.

Why would MakeQueryLayer_management return an empty object?

Thanks

Re: Why would MakeQueryLayer_management return an empty object?

LongDinh — Fri, 10 Sep 2021 01:06:28 GMT

Hi @JesseTemplin2 ,

According to the usage notes in the tool documentation:

If the result of the SQL query does not return any rows, the output query layer will be empty, only containing the schema of the columns returned by the query. In this case, if the columns returned contain a spatial column, the tool will use the following defaults to create the query layer:
Geometry type—POINT
SRID—1
Spatial Reference—NAD1983
Then you need to determine whether any of these values should be changed before executing the tool.

So perhaps the Query is not correct? Can you run the tool in ArcGIS Pro and copy the snippet to your python script and see if it works?

Re: Why would MakeQueryLayer_management return an empty object?

JesseTemplin2 — Fri, 10 Sep 2021 15:41:40 GMT

Same deal if I run the tool in ArcGIS Pro. The query is a simple select * from table. One sde connection creates a table and the other doesn't.

Re: Why would MakeQueryLayer_management return an empty object?

JesseTemplin2 — Thu, 23 Sep 2021 16:08:02 GMT

This appears to be a bug, not limited to MakeQueryLayer

BUG-000139927

Re: Why would MakeQueryLayer_management return an empty object?

PhilLarkin1 — Fri, 19 Apr 2024 21:55:27 GMT

Hello and welcome from the future. I am able to replicate this bug though in a different way. I get ERROR 99999 if I attempt to create a query layer using MakeQueryLayer when connected with a username that has a period. Thanks for posting it.

ArcGIS Pro: 3.1.2
DB: SQL Server 15