Changes in database not showing up in service/web map

[VirginiaSeamster]

Hello!

I have a map service that references a non-spatial table (more specifically a view) stored in a SQL Server database. This table is related to a spatial layer (i.e., feature class) in the map service. I have registered both the non-spatial SQL Server database and the file geodatabase containing the feature class with ArcGIS for Server. After publishing the map service, I made changes to the tables referenced by the view in SQL Server. These changes show up when I look at the table in the source mxd for the map service. However, these changes do not show up when I do a search of the spatial layer and try to view information in the related table in the web mapping application (ArcGIS Viewer for Flex) that I'm using to view the map service.

Do I need to overwrite the map service in order to get these changes to show up? I registered my data sources with ArcGIS for Server in an attempt to preclude the need for overwriting the map service every time the SQL Server database is updated. If I shouldn't need to do an overwrite of the map service, then do you have any suggestions for things I should look at/try out in order to identify the source of the problem?

Thank you in advance for any assistance you can provide.

Virginia

[lunlee]

I guess your request may hit the browser cache,try to append some extra ramdon number to your query string.

hope this helps..

[VirginiaSeamster]

Thank you for the recommendation Lun Lee.

I've been working with ESRI support, and others, on resolving this issue, and at this point it looks like the root of the problem *may* be related to how ArcGIS for Server is accessing the SQL Server instance- specifically the use of windows authentication vs. SQL server authentication. I assumed this mode of authentication was determined by the settings in the .sde connection file to the database that is set up from ArcCatalog...However- if there is another place to change this mode from windows/OS to SQL server/database authentication- including via ArcGIS for Server Manager, then I would very much appreciate any information that anyone on the forum can provide.

I have no problems doing a query of records in the related non-spatial table from the rest services directory *if* I don't register the non-spatial SQL Server database with ArcGIS for Server OR if I access tables in a SQL server instance on the same machine as ArcGIS for Server 10. Hosting SQL server on the same machine as ArcGIS for Server 10.1 isn't really possible given my current set up- so I need to make the connection to a remote instance of SQL server- ideally one that is stored on a different network than ArcGIS for Server- work. Given these observations though, it seems possible that the issue is related to firewall/network settings/permissions...when the non-spatial data is copied to ArcGIS for Server, there doesn't seem to be a problem.

Any further advice on this issue is much appreciated.

Thank you!

Virginia

[VirginiaSeamster]

One further update.

I was able to get the query of related non-spatial tables stored in a SQL server instance on the same network as ArcGIS for Server to work by doing the following:
1) Using operating system authentication in the .sde connection from ArcCatalog to the SQL server database.
2) Setting the ArcGIS for Server account (a domain account for my set up which has a windows authenticated login in SQL server) to have a db_owner role for the database I was trying to access.

My understanding is that it would be difficult to make the ArcGIS for Server account a user on the remote server/for the remote instance of SQL server since it is a domain account. So the options at this point appear to be to make ArcGIS for Server be a local account OR to set up a replicate of the remote SQL server database on the SQL instance that is on the same network as ArcGIS for Server.

Any thoughts on which of these options might be better or any suggestions for other options that I've missed are much appreciated.

Thank you!
Virginia

[VirginiaSeamster · Accepted solution]

Hello!

I am now able to do queries of related tables that are pulled from a remote SQL server database- which is on a different network- via the "query related records" function in the rest services directory.

I need to do more testing to pinpoint all the necessary elements but at this point these were the changes I made:
1) Set up a local (rather than domain) account for SQL server that matches the credentials for the ArcGIS for Server account and that is a sysadmin and has db_owner permissions for the database. This account uses windows rather than SQL server authentication when accessing the database.
2) Used operating system authentication when connecting to the remote SQL server database via ArcCatalog
3) Removed a definition expression that had been placed on the related table in the mxd file prior to publishing the map service. This last step is definitely necessary- again I'd have to do more testing to see which of the first two steps was needed in order for queries of the related tables in the map service to work.

Virginia

[VirginiaSeamster]

A final update- the SQL server login that matches the credentials for the ArcGIS for Server account needs to be data_reader on the SQL server database- sysadmin and db_owner are not necessary permissions.

Virginia