Select to view content in your preferred language

cross database views in SQL Server Express 2008

1050
1
07-22-2010 04:50 AM
GordonSumerling
Esri Regular Contributor
Hello All,

I have configured a copy of SQL Express to work with two databases, one has a Geodatabase/sde configured from ArcCatalog and the other has tables seperate from ArcGIS to which I want to make view to.

The following web page describes how to create cross-database views this using the sdetable command
http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/Views_in_SQL_Server/002q00000025000000...

but SDE Personal and SDE Workgroup do not deliver the SDE commands which this web page describes.

In my database I have created a cross-database view but the sde tables do not recognise this and thus the Geodatabase cannot register the table.

Is there a way to acheive cross-database views in SQL Server Express that can be recognised by the geodatabase?
0 Kudos
1 Reply
BastiaanSchupp
New Contributor
Hi Gordon,

Don't know if an answer is still relevant to you, but I will answer nevertheless.

I am using the full products (ArcSDE 9.3 and SQL Server), but still had problems with the official way to do it. It does not allow me to create network information for routing based on a view when ArcSDE knows it is a view. The weights I am using are frequently changed, and come... from another table.

Therefore, I did it in the unofficial way. ArcSDE apparently does not recognize whether it is looking at a real table or at a view. So, how to do it?

Say you have two tables: MyGeo and MyData, and you would actually want to create a view called MyGeoData, which complements MyGeo with data from MyData.

- First, using an ArcGIS product, add colums (fields) to MyGeo, which have exact the same datatype as the columns you want to view from MyData. In my case, I added a couple of columns most of them integers.  (alternatively you could also rename MyGeo to MyGeoData, the steps below would change slightly)

- To be sure, now stop the SDE service.

- in SQL rename the table MyGeo to something like MyGeo_source

- in SQL create a view called MyGeo (i.e. exactly the same name as the old SDE table you just renamed, if you took the alternative approach your previous step would have reversed the MyGeoData to MyGeo, and your view would now be called MyGeoData), and in the script create the join with MyData, and do any other manipulations you like...

(- You could now drop the extra columns you (via ArcGIS) created in the first step from MyGeo_source, but perhaps it is better to just leave them.)

- now, start the SDE service...

ArcSDE will now be looking at the view, without even knowing it. (you can also add the columns manually to the ArcSDE table administration without having them added to the MyGeo table, but this requires you to understand how ArcSDE administers its columns)


That's all there is to it. I guess this should work using the products you are using as well.

I don't understand why ESRI makes this process so difficult, since combining data in this way seems to me a very logical and frequent GIS application, which you do not necessarily want to do within ArcGIS. I have learned that manipulating large tables (millions of records, 100Gb) in SQL manager is by far the preferred option.
0 Kudos