I want to execute three joins in total. The first two joins work, but when I'm trying to join both resulting datasets I get the beloved 999999 ERROR.

# sr tables for joins
sr1Table = sr1Path + ".dbf"
sr2Table = sr2Path + ".dbf"
# create nw feature layer for join
nw1Layer = featureFolder + "nw1_layer"
nw2Layer = featureFolder + "nw2_layer"
arcpy.MakeFeatureLayer_management(nw1Path, nw1Layer)
arcpy.MakeFeatureLayer_management(nw2Path, nw2Layer)

# execute join on ID, sr1_layer will be joined to the nw1_layer
arcpy.SetProgressorLabel("Joining NW1 with SR1 ...")
arcpy.AddJoin_management(nw1Layer, "ID", sr1Table, "ID")
# execute join on ID, sr2_layer will be joined to the nw2_layer
arcpy.SetProgressorLabel("Joining NW2 with SR2 ...")
arcpy.AddJoin_management(nw2Layer, "ID", sr2Table, "ID")
# execute join on ID, nw2_layer will be joined to the nw1_layer
arcpy.SetProgressorLabel("Joining NW1 with NW2 ...")
arcpy.AddJoin_management(nw1Layer, "ID", nw2Layer, "ID")

Is this because I again have to create Feature Classes of nw1Layer + nw2Layer with CopyFeatures_management, then again use MakeFeatureLayer_management on the new Feature Classes and use these for the 3rd join? I try to avoid this, because I work with huge Shape Files and creating those Feature Classes would cost many hours of time.

  • Have you tried to create a feature class prior to the 3rd join? You can use in_memory workspace for faster performance (desktop.arcgis.com/en/arcmap/10.3/analyze/modelbuilder/…)
    – Bera
    Commented Jun 29, 2017 at 9:56
  • If I create feature classes prior to the 3rd join, it works. I use the in_memory workspace and it really goes faster... but if I work with big shape files, I get the ERROR 999998, despite having enough only about 40% memory usage at peaks.... so there is no other option than creating the feature classes first?
    – Flugmango
    Commented Jun 30, 2017 at 8:24
  • Try converting the shapefiles to feature classes in a file geodatabase and then join etc
    – Bera
    Commented Jun 30, 2017 at 9:35

2 Answers 2


Have you checked how the "ID" fields are named in the two layers (nw1, nw2)? When joining them with the table, they may be prepended with the input table name. So, in nw1Layer, the "ID" fields may look like "sr1.ID" and "nw1.ID" (depending on the actual table names). As a result, the final AddJoin_management may not be matching the simple "ID" with the actual joined layer's field names.

You can check this by adding a line like this after each of the first joins:

print [f.name for f in arcpy.ListFields(layer)]

For more information, see the documentation about qualified field names: http://pro.arcgis.com/en/pro-app/tool-reference/environment-settings/qualified-field-names.htm). This includes information about how to modify this behaviour using environment settings.

  • i added env.qualifiedFieldNames = False on top of the script, so the field names don't get prepended with the table names
    – Flugmango
    Commented Jun 30, 2017 at 8:15

The solution was to again use CopyFeatures_management on the resulting feature layers to create new feature classes and MakeFeatureLayer_management to create new feature layers and use these for the final join. I also used the in_memory workspace instead to keep the performance at an acceptable level.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.