IFeatureDataConverter.ConvertFeatureClass just hangs on SQLServer to FeatureCLass

2399
9
03-05-2021 03:50 PM
AbelPerez
Occasional Contributor III

My company recently moved our SQL Server from one location to another and also updated it from V11 to V13. I have a .NET app that takes data from the SQL Server table and converts it to a point FeatureClass in a local FileGeoDB. The process has worked great up until the server move.

I've setup a connection to the new SQL Server and that works. I can enumerate the tables on the source database. So am confident the connection to the new SQL Server is solid.

Dim pWkspSource As IWorkspace = OpenSqlDbWorkspace(ci)

I setup  up my target workspace, dataset, and feature class and then I attempt to convert via 

Dim pFeatDataConverter As IFeatureDataConverter = New FeatureDataConverter

Dim pEnumInvldObj As IEnumInvalidObject = pFeatDataConverter.ConvertFeatureClass(fcSourceName, pQryFltr, fdTargetName, fcTargetName, Nothing, Nothing, "", 1000, 0)

At this point the process just hangs at ConvertFeatureClass with no errors or exceptions.

What could be the issue and/or what can I try to troubleshoot the issue?

0 Kudos
9 Replies
KirkKuykendall1
Occasional Contributor III

Are you able to copy and paste the featureclass successfully within sql server in the catalog view of arcmap?

0 Kudos
AbelPerez
Occasional Contributor III

The source is not a feature class, only a Table. In .NET I define it as an XYTable and with a QueryFilter that gets converted to a Feature Class. Let me see if I can do that  manually with a small subset.

0 Kudos
AbelPerez
Occasional Contributor III

Ok her are my tests: The db/table I am working with is [WorldData].[dbo].[Obstacles] and has ~11.5 million records. In ArcMap 10.8.1 here is what I do to work with the data:

1. In the Catalog I use Add Database Connection, I enter my info and "AcmeCo002.sde" is created.

2. I double-click on the item to connect to the db and the available tables are shown in the Catalog.

3. I drag the table [Obstacles] to the map and it shows up as a ITable in the TOC.

4. At this point I know I want a small subset of the data so I right-click on the ITable and under Definition Query I enter: (xpos>=-9.5 AND xpos<=-8.6) AND (ypos>=38.3 AND ypos<=39.0). Using this same exact filter in SSMS gets me 8000 records. I click on the Verify button and it says all is good.

AbelPerez_0-1615072336369.png

5. I then want to view this small subset so I right-click on the ITable and choose Display XY Data. This process took several hours at the very least! I left after 2 hours and came back in the morning but there was an event layer created.

AbelPerez_1-1615078049417.png

6. Displaying the event layer takes a veryyyyyyyyy longggg time which to me says that ALL the records were exported and not just those in the Definition Query of the ITable. Sure enough after about 20 minutes all records in the table are displayed. All 11.5 million of them.

7. This is not how the ITable conversion worked in the past or how it's intended to work. Why would I need all 11.5M records when my Query Definition filters to 8K records?

8. This is probably why my .NET code is "hanging" because the IQueryFilter is not being applied when the ConvertFeatureClass executes. It sounds like its copying all 11.5M records and just taking hours to process.

0 Kudos
KirkKuykendall1
Occasional Contributor III

That's strange.  Maybe a bug?  If so, it might take a while for it to get fixed.  Have you tried it in ArcGIS Pro?

As a workaround, consider creating a view in SQL server ...

 

create view testxyPoints as 
Select testid
	,x
	,y	
	,geometry::Point(x,y,3857) as shape
from Testxy;

 

And then in Arcmap, instead of adding an XY layer, add an SQL query layer. (File> Add Data> Add Query Layer)

KirkKuykendall1_0-1615141628626.png

Then see if FeatureDataConverter can digest that.

You can also do it without creating a view ...

KirkKuykendall1_0-1615174471221.png

 

0 Kudos
AbelPerez
Occasional Contributor III

The app actually uses ArcObjects SDK for automation so moving to Pro is not an option at this time. Also I do not the dba of the SQL Server so getting a view implemented would take some effort and time. I guess my only move here is to file an incident.

0 Kudos
KirkKuykendall1
Occasional Contributor III

You can also do it without a view ...

KirkKuykendall1_0-1615174591270.png

 

0 Kudos
KirkKuykendall1
Occasional Contributor III

Check the "Define spatial properties..." to speed things up, the "discover" option does a full scan of the query.

0 Kudos
AbelPerez
Occasional Contributor III

Thanks for all your help!

So on this test using the Query Layer I chose my connection and then my SELECT with my WHERE. It complains that my table doesn't have an OID, which it doesn't. I just choose STATEID which is a number but not unique to each record.

It converts it to an ITable. Then I can choose Display XY Data and I can now see that my selection of points is where they should be and it is definitely NOT the 11.5 million points in the source table.

So I'm getting close.

 

0 Kudos
AbelPerez
Occasional Contributor III

I've connected to another SQL Server and that one also fails on 

Exception thrown: 'System.Runtime.InteropServices.COMException' in MyApp.exe
System.Runtime.InteropServices.COMException (0x80040205): Create output feature class failed
at ESRI.ArcGIS.Geodatabase.IFeatureDataConverter.ConvertFeatureClass(IFeatureClassName InputDatasetName, IQueryFilter InputQueryFilter, IFeatureDatasetName outputFDatasetName, IFeatureClassName outputFClassName, IGeometryDef OutputGeometryDef, IFields OutputFields, String configKey, Int32 FlushInterval, Int32 parentHWND)
at ....


0 Kudos