ArcSDE and SQL relates

Question

Does anyone know if ArcGIS honors MSSQL relationships that are not built in SDE? How about general MSSQL column types. In the grand scheme I think SDE limits the types to few where MSSQL choices are many. I would like to see ArcSDE and MSSQL make use of dbms for non spatial data, i.e. a parcel feature class (SDE) related to CAMA data (non SDE) without having to build a spatial view.

GIS-Cambria · Answer

All:

Thanks for the input for query layers. I have successfully created a query layer, mxd with relate (parcel feature class to ownership query layer), AGS service, and AGS application. I was very happy to see that the relate was honored in both the AGS Service (and REST), and in the OTB web application. I tested query tasks built on the query layer in the .net app, and was pleased to get results. Also, using search attributes task on the parcel layer with the parcel-ownership relate returned the linked results. KUDOS ESRI for finally giving rdbms capability to AGS!

GIS-Cambria · Answer

Ted and russellb

Thank you both for pointing to Query Layers. I was primarily looking for ways to use related data that is not registered with the GDB....AND hoping that I could figure out a way to symbolize or follow the relates in published services. The CAMA I referred to is edited outside of GIS, but currently linked to parcel polys by a spatial view. I've had trouble with SDE playing nicely with the CAMA table since it's not registered, no aliases, problems with annotation feature classes..... But the query layer may be the ticket. I'll have to try the query layer in Beta; if it works in 9.4, as the help indicates, that is good news.

TedCronin · Answer

Does anyone know if ArcGIS honors MSSQL relationships that are not built in SDE? How about general MSSQL column types. In the grand scheme I think SDE limits the types to few where MSSQL choices are many. I would like to see ArcSDE and MSSQL make use of dbms for non spatial data, i.e. a parcel feature class (SDE) related to CAMA data (non SDE) without having to build a spatial view.

You may want to look into query layers.  These are layers you create using fields that are spatial in nature, but not sde.  How about relationship classes based on Assessment Numbers, or build a database view in SQL,  and join interactively with an MXD.  Are you trying to edit the CAMA data inside of ArcGIS, and having to work with all of the sde based tables and how the data crosses between the different databases?  Or are you just wanting to use CAMA tables with GIS data?