arcgisscripting.ExecuteError: ERROR 000223: Cannot extract event table properties Failed to execute (MakeXYEventLayer)

Question

import arcpy
import sys
import os

#arcpy.env.workspace = "C:\\Test\\addresspoint"
#arcpy.env.overwriteOutput = True

input_database = "C:\\Test\\addresspoint\\connection\\Test.sde"
out_layer_name = "addPointtbv"

Query = """ SELECT S_ID, S_Key, Address, City, State, ZipCode, Latitude, Longitude FROM [ST].[dbo].[ST] """
Unique_Identifier_Field_s_ = ["S_ID", "S_Key"]
Shape_Type = "POINT"
SRID = "3857"
Coordinate_System = """PROJCS['WGS_1984_Web_Mercator_Auxiliary_Sphere',GEOGCS['GCS_WGS_1984',DATUM['D_WGS_1984',SPHEROID['WGS_1984',6378137.0,298.257223563]],PRIMEM['Greenwich',0.0],
UNIT['Degree',0.0174532925199433]],PROJECTION['Mercator_Auxiliary_Sphere'],PARAMETER['False_Easting',0.0],PARAMETER['False_Northing',0.0],PARAMETER['Central_Meridian',0.0],
PARAMETER['Standard_Parallel_1',0.0],PARAMETER['Auxiliary_Sphere_Type',0.0],UNIT['Meter',1.0]];-20037700 -30241100 10000;-100000 10000;-100000 10000;0.001;0.001;0.001;IsHighPrecision"""

# Process: Make Query Layer (Make Query Layer) 
add_pnt = arcpy.MakeQueryLayer_management(input_database, out_layer_name, Query, Unique_Identifier_Field_s_,Shape_Type, SRID, Coordinate_System)                                              
print(out_layer_name + " query layer is created")

X_Field="Latitude"
Y_Field="Longitude"  
Spatial_Reference = Coordinate_System

# Process: Make XY Event Layer (Make XY Event Layer) 
in_layer = out_layer_name
out_layer = "xyeventlayer"

arcpy.MakeXYEventLayer_management(in_layer, X_Field, Y_Field, out_layer, Spatial_Reference)
print(out_layer + " is saved")

Error:

arcgisscripting.ExecuteError: ERROR 000223: Cannot extract event table properties

Failed to execute (MakeXYEventLayer).

It does work in ArcGIS Pro based python 3.6.9 (64 bit) anaconda, It is not working at all in 2.7.5 and 2.7.16 which desktop based installation and it is 32 bit.

Answer 1

According to documentation, spatial reference argument should be a SpatialReference object. It also accepts some string type of spatial reference. But it may not accept wkt string of spatial reference like in your script.

Therefore, try to create SpatialReference object.

# other lines

coordinate_system_string = """PROJCS['WGS_1984_Web_Mercator_Auxiliary_Sphere',GEOGCS['GCS_WGS_1984',DATUM['D_WGS_1984',SPHEROID['WGS_1984',6378137.0,298.257223563]],PRIMEM['Greenwich',0.0],
UNIT['Degree',0.0174532925199433]],PROJECTION['Mercator_Auxiliary_Sphere'],PARAMETER['False_Easting',0.0],PARAMETER['False_Northing',0.0],PARAMETER['Central_Meridian',0.0],
PARAMETER['Standard_Parallel_1',0.0],PARAMETER['Auxiliary_Sphere_Type',0.0],UNIT['Meter',1.0]];-20037700 -30241100 10000;-100000 10000;-100000 10000;0.001;0.001;0.001;IsHighPrecision"""

coordinate_system = arcpy.SpatialReference()
coordinate_system.loadFromString(coordinate_system_string)

add_pnt = arcpy.MakeQueryLayer_management(....., SRID, coordinate_system)

# other lines

arcpy.MakeXYEventLayer_management(....., coordinate_system)

Answer 2

Finally after struggling with code and debugging more than 100 times and talk to ESRI support I am able to resolve this issue, which is a bug listed in Esri BUG-000126550. This bug is not in XY event layer it is in Make query layer. So instead of using

arcpy.MakeQueryLayer_management(......) 

I have to use

arcpy.CreateDatabaseView_management (input_database, view_name, view_definition)

---

import arcpy
import sys
import os

#arcpy.env.workspace = "C:\\Test\\addresspoint"
#arcpy.env.overwriteOutput = True

input_database = "C:\\Test\\addresspoint\\connection\\Test.sde"
view_name = "db_view"

view_definition = """ SELECT S_ID, S_Key, Address, City, State, ZipCode, Latitude, Longitude FROM [ST].[dbo].[ST] """

SRID = "3857"
# Process: Create Database View
arcpy.CreateDatabaseView_management (input_database, view_name, view_definition)                                    
print(view_name + " db view is created")

X_Field="Latitude"
Y_Field="Longitude"  
# Process: Make XY Event Layer (Make XY Event Layer) 
in_layer = view_name
out_layer = "xyeventlayer"

arcpy.MakeXYEventLayer_management(in_layer, X_Field, Y_Field, out_layer, 3857)
print(out_layer + " is saved")