Geocode a Microsoft SQL Server View

284
9
12-07-2021 12:18 PM
MichaelVolz
Esteemed Contributor

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?

0 Kudos
9 Replies
ABishop
Regular Contributor III

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?

Amanda Bishop, GISP
0 Kudos
MichaelVolz
Esteemed Contributor

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?

0 Kudos
ABishop
Regular Contributor III

What version of ArcMap?

Amanda Bishop, GISP
0 Kudos
MichaelVolz
Esteemed Contributor

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).

0 Kudos
ABishop
Regular Contributor III

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?

Amanda Bishop, GISP
0 Kudos
MichaelVolz
Esteemed Contributor

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.

0 Kudos
ShanaBritt
Esri Regular Contributor

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?

0 Kudos
MichaelVolz
Esteemed Contributor

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).

0 Kudos
ShanaBritt
Esri Regular Contributor

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.

0 Kudos