Publishing Live Data to Silverlight

[deleted-user-yA_w_FC9FKe5]

I am using the Silverlight Builder to create a quick application to show where our stores are located.  This all works great if I add the SQL table to a Geodatabase as a Feature class.  I then add it into the builder and boom looks great.

The problem is that it is not live.  So if I delete records out of the SQL table nothing changes in the geodatbase or the service.  I was told this can be made to be live but after hours and hours of searching on the internet I am starting to lose hope.  This is really important to us.  Can someone walk me through the steps on how to do this.  This should be possible and easy right?

[IgressT]

look at this link http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/What_is_a_query_layer/00s50000000n0000...

I have similar situation... I have a location table in SQL Server 2008 with latitude and longitude columns and points gets added/removed all the time. Since the table is a part of a bigger database I cannot convert it into a GDB.

However, in ArcMap (not sure you have access to it) you can add a query layer directly read from a sql server database. Publish that as a dynamic service. We have another application that actually inserts/deletes the data from this table. Since this being a dynamic service any changes made to the table are shown on the map (well actually I provide a refresh button and as soon as user clicks refresh he can see the changes)

[deleted-user-yA_w_FC9FKe5]

Thanks for the information on the Query Layer.  I feel like I am sooooo much closer now.

I do have ArcMap and I did create the query layer.  Tested to make sure it was all live and it is working great.  However, when I went to publish the map via ArcGIS server I am getting this error "Configuration Stores_Live_Data.MapServer can not be started."

However, I think you want me to do something different now that I have it as a XY event layer.  Can you walk me through the dynamic service piece?

[IgressT]

This is what I did when I created my service and it works for me

1. Create a new login with SQL Server Authentication in SQL Server 2008 (my SQL Server is on network)
2. In ArcMAP>File>Add Data>Add Query Layer>Click Connections>Click New>Enter Server details and in account
3. Change Authentication to Database Authentication and provide the new login details and click Test connection and make sure it succeeds
4. In the name give a query name
5. In the Query enter something like this (unless you are creating a stand alone table)
SELECT *,  geography::STPointFromText('POINT(' + CAST(Longit AS VARCHAR(20)) + ' ' + CAST([Latit] AS VARCHAR(20)) + ')', 4326) As Loc     
FROM TableXXX 
6. Click validate to check for errors.
7. You can now see data in ArcMAP, save the document
8. From ArcCatalog select the document and publish it as a service.

In which step are you getting the error...

[deleted-user-yA_w_FC9FKe5]

This is very cool but when I do this I can see my points in the spatial results but not on my map. 

SELECT objectid, FDSID, STORENAME, LATITUDE, LONGITUDE, geography::STPointFromText('POINT(' + CAST(LONGITUDE AS VARCHAR(20)) + ' ' + CAST(LATITUDE AS VARCHAR(20)) + ')', 4326) As Loc
FROM GIS.Stores_GEO

However, I still tried to publish the map and I am still getting this same error.  I get the error when I try to publish the map to the ArcGIS Server (SDE).  All I do is this.

1.) Open ArcCatalog
2.) Locate Map
3.) Right Click and select Publis to ArcGIS Server
4.) I leave all of the defaults
5.) Click finish and get the error

[IgressT]

Looks like you are using the Query layer from a table stored in a geodatabase.... I don't know if it will work... the documentation says query layers work directly off of a DBMS and you don't need geodatabase.

[deleted-user-yA_w_FC9FKe5]

No I am using the File/Add Data/Add Query Layer that you suggested.  When I bring it in without the spatial reference your query is using things it look fine in ArcMap.  I just have an extra step of making it an XYEvent. 

My Query:
"SELECT     OBJECTID, FDSID, STORENAME, LATITUDE, LONGITUDE
FROM         GIS.Stores_GEO"

Either way when I go to publish I get that same error about not being able to start it 😞

[IgressT]

No I am using the File/Add Data/Add Query Layer that you suggested.  When I bring it in without the spatial reference your query is using things it look fine in ArcMap.  I just have an extra step of making it an XYEvent. 

My Query:
"SELECT     OBJECTID, FDSID, STORENAME, LATITUDE, LONGITUDE
FROM         GIS.Stores_GEO"

Either way when I go to publish I get that same error about not being able to start it 😞

Ok... confusing... but
1. No need to create an XYEvent
2. use SELECT objectid, FDSID, STORENAME, LATITUDE, LONGITUDE, geography::STPointFromText('POINT(' + CAST(LONGITUDE AS VARCHAR(20)) + ' ' + CAST(LATITUDE AS VARCHAR(20)) + ')', 4326) As Loc
FROM GIS.Stores_GEO  instead of what you provided