2

I'm using an SQL query (from MS SQL Server) on an enterprise DB connection, and am able to create a stand-alone table from the "Make Query Layer" tool.

After this table (stand-alone query table) is created, I want to export it to a file geodatabase, but whenever I use Table to Geodatabase conversion, I always get the error of "Failed to convert TableName. ERROR 999999: Error executing function. Failed to execute (CopyRows).

I know if I manually right click the table, export -> Data, I am able to do this. But I want to script this in ArcPy to do it, but the conversion won't work. I even specified Unique Identifiers but am hitting road blocks.

I am using ArcGIS Desktop 10.4.1. My end goal is to essentially use an SQL query to get a table (non-spatial), and input that table into a FGDB (as a FGDB table).

All other tools like Table to Table results in the exact same error. Parameters only include my input rows (which is the result from Make Query Layer) and output FGDB. I have not scripted anything in Python yet as I am trying everything in ArcGIS Desktop first to see if it works there.

Within MS SQL, it is a normal (non-ESRI) database. For the "Unique Identifier Fields" I tried multiple instances, I used one (unique) field "AssetID", a combination of 2-3 field "Field1", "Field2","Field3", and i also tried leaving it blank (all unselected).

  • 1
    There are some issues with how you're describing your flow. A Query Layer is a display-oriented object in the ArcGIS UI with a geometry column and one or more rowid columns (plus other attribute columns). Yet your second paragraph starts "After this table is created" -- Which table is being created? The Table to Geodatabase tool expects a non-spatial table, not a Query Layer. Please Edit the question to specify the RDBMS and version of ArcGIS Desktop in use, and describe your end goal, not the process you have taken. – Vince Aug 8 at 19:29
  • 1
    Have you tried just right-clicking on the table in the Catalog explorer and choosing "Export" -> "To Geodatabase (single)" or "To Geodatabase multiple)"? This is basic function for PostgreSQL databases (I haven't used SQL Server in nearly a decade). – Vince Aug 8 at 20:02
  • 1
    No, your post referenced the Data link of a layer, not the Table to Table from Catalog (which is scriptable with arcpy.TableToTable_conversion) – Vince Aug 8 at 20:07
  • 1
    Lots of potential causes to the six nines error; rewriting this question to focus on that is probably your best route to a solution, but that would require the SQL definition of the table. The workaround might be to create an arcpy.ArcSDESQLExecute cursor and process that through an arcpy.da.InsertCursor but that will only work if the datatypes are supported. – Vince Aug 8 at 20:19
  • 2
    Work the problem first, but the problem isn't using a Query Layer to export a table; the problem is using TableToTable against a SQL Server table. – Vince Aug 8 at 20:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.