Add SQL Server view to ArcMap as XY event – Problem resolved

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