In the past week I came acros a problem of displaying X,Y event based on quite complicated, multitables, multiowners query.
Usually I work with Oracle database, however this time I had to pull out the data from SQL Server database.
I have created sucessfully the View, and served the X,Y data as the MapService through the ArcGIS Server – everything was fine until I had to relate another table and the problems jumped out as there was no OBJECTID field presented in the view.
I dig a bit and found a solution of using indexed Views (http://strangenut.com/blogs/dacrowlah/archive/2008/11/26/creating-an-indexed-view-in-sql-server-2005-and-2008.aspx) however in my case I was getting:
"Index cannot be created on view '....View_Ix' because the underlying object '...._origin' has a different owner." – that was probably because I had several tables from different users in my query, anyway…
After few hours of checking diferent things I have discovered that if you use the “Make Query Table” tool from ArcToolBox before you make your XY event layer and specifiy which field to use as a key in that tool, it works just great.
I need to test few things yet – so check if the data is being updated properly in that new table etc, but at the moment everything looks promissing.
I hope that hint will be useful for some of you as well.