Possible to link Geodatabase to SQL Server to view and or edit real time data

[KristenJones1]

This is something I have needed to do for years and it seems with each new release of Server software we get closer and closer. I have a perfectly working ArcGIS Server 10.1(SP1) installed and a ArcSDE geodatabase connected and working and no problems. I can create and edit feature classes, but I REALLY need to display a data value from another SQL server/Database. All I want to be able to do is create a join on a table from this other database to show the current values of a joined record. For example I have a list of Wells in a Geodatabase and in another SQL server I have the current levels that are updated daily. I would like to be able to create a ArcGIS.com map that will have a popup on the well and then pull the current level from the other SQL database.  I can do this all day long in ArcMAP but I need to make a real world map to do this.  I have been able to create a LinkedServer in the GeoDB to query the other database and I can also create a VIEW and do a join of the database between the GeoDatabase table and the other server, but I have not been able to create a map service that will not fail to publish.

Is there any ways, back door, work arounds that would not require me to upload data and republish maps so that we can view real time data?

Thanks!

[TanuHoque]

Jason,
can you please send me the schema of both tables - more specifically the fields you are using to join and the field (including field type) that you need to determine which record is the current for each well?

Thanks.
Tanu

[KristenJones1]

Thanks for the reply, however it really does not matter, because I would create all new tables if it was possible to connect. The problem is that no matter what I do in any scenario, if I try to join anything outside of the actual Geodatabase, the publish fails.  I was just hoping of some work around or a way to do this.

[GeomatiqueTI]

Did you find a solution?
Thanks

[KristenJones1]

No,  I have been looking for a solution for years, personally I don't think there is one.  That surprises me given the huge need for editing real time data and tying into existing database infrastructure.

[ZacharyHart]

No,  I have been looking for a solution for years, personally I don't think there is one.  That surprises me given the huge need for editing real time data and tying into existing database infrastructure.

I believe the Spatial Data Server is what facilitates this functionality.

[KristenJones1]

It was always my understanding that Spatial Data Server was for connecting to and serving spatial databases, in my case the databases I need to access are not Spatial, they are just plan 'ole SQL databases. I would also still need to "link" the spatial data from the GeoDatabase features.  If I'm wrong I'd love to see an example, case study, white paper or something that outlines how it works and the capabilities. I'm thinking that the only way to do this type of display and editing capabilities is on a post process outside of the actual map and feature services...  like Silverlight or Flex?

[ZacharyHart]

Ah, i see what you mean now. What if you created an OLEDB connection to the non-spatial DB, I believe you can publish that way?

http://forums.arcgis.com/threads/24893-ArcGIS-Conecction-OLE-DB-Publishing-MapService-Problem

Search "ArcGIS Server OLEDB connection publish"

[KristenJones1]

Thanks for the link, that describes what I want to do and the problem in a nutshell, however it is referring to ArcGIS vers 10 and under.  I'm using 10.1 which is now completely different. You don't turn MXDs into services anymore.... and a lot of other core changes, like having to use arcGIS to publish a map rather than on your own server.

[ZacharyHart]

Thanks for the link, that describes what I want to do and the problem in a nutshell, however it is referring to ArcGIS vers 10 and under.  I'm using 10.1 which is now completely different. You don't turn MXDs into services anymore.... and a lot of other core changes, like having to use arcGIS to publish a map rather than on your own server.

Did you register your nonspatial database with your GIS Server?

EDIT: tested and I can get related data to publish (even related data out in a nonspatial SQL table) but no editing.