topic best practice when connecting to SQL DB in Data Management Questions

best practice when connecting to SQL DB

clt_cabq — Tue, 09 Jul 2024 14:21:08 GMT

I am able to connect to a SQL Server database (not a GDB) as either an OLE DB connection or as an SDE connection (using the New Database Connection option in Catalog) - is there an advantage to using one over the other? My use case is mainly extracting a table (approx 250K records) via a python script before applying various manipulations, merging with other data and loading into a GDB.

Re: best practice when connecting to SQL DB

George_Thompson — Tue, 09 Jul 2024 14:27:22 GMT

I would connect as the .SDE connection if using Python in Pro. I think this would provide you the easiest user experience.

I am not sure what the difference is between the 2 are, but usually only make .sde connections to non-geodatabases in SQL Server.

Just know that there are some limits to what can be done via ArcGIS client and .sde connections to non-geodatabases: https://pro.arcgis.com/en/pro-app/latest/help/data/databases/databases-and-arcgis.htm

Re: best practice when connecting to SQL DB

clt_cabq — Tue, 09 Jul 2024 14:35:19 GMT

@George_Thompson thanks for the response, this is what I suspected would be the answer. I did find one bit of functionality available as an SDE connection versus the OLE DB method - Summarize fields doesn't seem to be enabled in the OLE DB connection but it is in the SDE, so that makes a big difference when exploring data for me.

Re: best practice when connecting to SQL DB

George_Thompson — Tue, 09 Jul 2024 14:40:57 GMT

That makes sense. I would think that the .sde connection would work with most GP tool available. The OLE DB connection would have limited functionality with tools.