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?
Are you able to copy and paste the featureclass successfully within sql server in the catalog view of arcmap?
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.
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.
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.
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.
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)
Then see if FeatureDataConverter can digest that.
You can also do it without creating a view ...
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.
You can also do it without a view ...
Check the "Define spatial properties..." to speed things up, the "discover" option does a full scan of the query.
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.
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 ....