topic Re: Geocode a Microsoft SQL Server View in Geoprocessing Questions

Geocode a Microsoft SQL Server View

MichaelVolz — Tue, 07 Dec 2021 20:18:53 GMT

I have a python script that geocodes a SQL Server view (not a geodatabase) where all database elements are available through the database connection. As per company policy this is no longer allowed and select only access to just the view is now available. I can see all the tabular records in the view in ArcCatalog when using the new database connection, but the geocode table processing no longer generates geocoded points (Just an empty feature class).

Does anyone know what other access would be needed in order for the geocode table process to run again?

Do I also need access to the base tables that the view is comprised of?

Re: Geocode a Microsoft SQL Server View

ABishop — Tue, 07 Dec 2021 20:24:20 GMT

Hello Michael,

The permissions on a view shouldn't affect your ability to geocode the data. Does your python script have sufficient permissions or the correct path to the view?

Re: Geocode a Microsoft SQL Server View

MichaelVolz — Tue, 07 Dec 2021 20:27:41 GMT

The issue occurs when I manually use the tool in ArcMap as well, so it is not a permissions issue with the script but the SQL Server database connections privileges.

Do you have any data in your environment to try to replicate my setup and see if the same issue occurs for you?

Re: Geocode a Microsoft SQL Server View

ABishop — Tue, 07 Dec 2021 20:29:25 GMT

What version of ArcMap?

Re: Geocode a Microsoft SQL Server View

MichaelVolz — Tue, 07 Dec 2021 20:34:34 GMT

10.7.1.

If you have the ability to replicate this setup but only have access to a version of Pro, that should work as well. I honestly don't think this is an ArcMap or Pro version issue, unless it is a documented bug that has been fixed in Pro or a higher version of ArcMap (That I am unaware of).

Re: Geocode a Microsoft SQL Server View

ABishop — Tue, 07 Dec 2021 20:36:41 GMT

I have access to a view that contains address that I could possibly geocode. What address locator are you using? Custom or world? Also, When you perform the geocode, were are you storing the output?

Re: Geocode a Microsoft SQL Server View

MichaelVolz — Tue, 07 Dec 2021 20:41:37 GMT

I am using a custom address locator. I am saving the results out to a local drive for my python script and I was also saving to a local drive when I was performing the operation manually in ArcMap.

I have error tracking in my python script, but the generation of an empty feature class only throws a warning so I was initially unaware that the python script was not working as expected.

Re: Geocode a Microsoft SQL Server View

ShanaBritt — Wed, 15 Dec 2021 08:44:07 GMT

Are you saving out to a shapefile or file geodatabase locally?

What is the geometry type listed in the Shape field of the geocode result?

What is the warning that you get? Is it an esri warning code?

Re: Geocode a Microsoft SQL Server View

MichaelVolz — Wed, 15 Dec 2021 20:40:51 GMT

Shana:

I am saving out to a file gdb into a point feature class. The warning says an empty dataset has been created, which because it is not an error, does not get trapped with my python error code. I am only reading in the view, so it makes no sense to me that Update privileges are required as per testing with ESRI tech support (Incident # 02947806).

Re: Geocode a Microsoft SQL Server View

ShanaBritt — Thu, 16 Dec 2021 00:11:31 GMT

Michael:

What type of connection did you make to your non-spatial SQL Server in ArcMap?

Did you make a connection as described here https://desktop.arcgis.com/en/arcmap/10.7/manage-data/databases/connect-sqlserver.htm (Pro option, https://pro.arcgis.com/en/pro-app/latest/help/data/databases/connect-sqlserver.htm) or has a geodatabase been enabled in the SQL Server database.