Create spatial view from two different databases?

[MichelleBoivin]

I am in the process of setting up our spatial views in ArcGIS 10.1 SP1, ArcSDE 10.1 SP1, SQLServer 2008R2. Due to some other geometric network issues, we have to have all of our data in SDEBINARY and cannot store it in Geometry at this time (currently this is non-negotiable). According to ESRI, in order to create a spatial view with SDEBINARY data you have to use the command line sdetable -o create_view. I am able to do this when both feature class and table are in the same database, same instance, but I need to be able to set up a spatial view where the feature class is in one database and the flat table is in another database, same instance.

The correct syntax which created the view with one database (WATER)/two objects is:
sdetable -o create_view -T PLN_DEVELOP_FEE_AREAS_VIEW -t PLN_DEVELOP_FEE_AREAS,FFCIMPORT -c PLN_DEVELOP_FEE_AREAS.Shape,PLN_DEVELOP_FEE_AREAS.IDNumber,FFCIMPORT.IDNum,FFCIMPORT.ProjectNum,PLN_DEVELOP_FEE_AREAS.ObjectID -w "IDNumber=IDNum" -i sde:sqlserver:SQL04V\GIS01 -s SQL04V\GIS01 -D WATER

I thought I should be able to do the following with two databases (WATER & GIS_REPOSITORY_TABLES):
sdetable -o create_view -T PLN_DEVELOP_FEE_AREAS_VIEW -t PLN_DEVELOP_FEE_AREAS,GIS_REPOSITORY_TABLES.DBO.FFCIMPORT -c PLN_DEVELOP_FEE_AREAS.Shape,PLN_DEVELOP_FEE_AREAS.IDNumber,GIS_REPOSITORY_TABLES.DBO.FFCIMPORT.IDNum,GIS_REPOSITORY_TABLES.DBO.FFCIMPORT.ProjectNum,PLN_DEVELOP_FEE_AREAS.ObjectID -w "IDNumber=GIS_REPOSITORY_TABLES.DBO.IDNum" -i sde:sqlserver:SQL04V\GIS01 -s SQL04V\GIS01 -D WATER

or

sdetable -o create_view -T PLN_DEVELOP_FEE_AREAS_VIEW -t WATER.DBO.PLN_DEVELOP_FEE_AREAS,GIS_REPOSITORY_TABLES.DBO.FFCIMPORT -c WATER.DBO.PLN_DEVELOP_FEE_AREAS.Shape,WATER.DBO.PLN_DEVELOP_FEE_AREAS.IDNumber,GIS_REPOSITORY_TABLES.DBO.FFCIMPORT.IDNum,GIS_REPOSITORY_TABLES.DBO.FFCIMPORT.ProjectNum,WATER.DBO.PLN_DEVELOP_FEE_AREAS.ObjectID -w "WATER.DBO.IDNumber=GIS_REPOSITORY_TABLES.DBO.IDNum" -i sde:sqlserver:SQL04V\GIS01 -s SQL04V\GIS01 -D WATER

Could someone please weigh in on this? I was able to do it before we moved to 2008R2 as well as 10.1.

Thank you for your help,
Michelle

[VinceAngelo]

Esri hasn't supported cross-database queries in a long time.  Your work-around is
to make an empty table with same layout in the local database, join to that, and then
edit the join.  Be aware, though, that the cross-database performance cost will be
daunting.

- V

[MichelleBoivin]

Esri hasn't supported cross-database queries in a long time.  Your work-around is
to make an empty table with same layout in the local database, join to that, and then
edit the join.  Be aware, though, that the cross-database performance cost will be
daunting.

- V

Vince, is this a 10.1 limitation? Below is a spatial view from 10.0 SP5, SQL 2005 that works where the table is stored in a separate database from the feature class (TPW vs. CFWGIS)....

SELECT     sde.TRN_STREET_PMA.Shape, sde.TRN_STREET_PMA.OBJECTID, sde.TRN_STREET_PMA.SECT_NO, TPW.dbo.PMA_STREETS.Street_Name, TPW.dbo.PMA_STREETS.gis_dir AS Dir, TPW.dbo.PMA_STREETS.Divided AS Divided_Street, TPW.dbo.PMA_STREETS.Storm_Water_Needs, TPW.dbo.PMA_STREETS.Traffic_Lane
FROM         sde.TRN_STREET_PMA INNER JOIN TPW.dbo.PMA_STREETS ON sde.TRN_STREET_PMA.SECT_NO = TPW.dbo.PMA_STREETS.sect_no

[VinceAngelo]

I believe optional cross-database access was eliminated at ArcSDE 9.1.  You'd have to go
back into 8.x for the last time I named a database "sde", so I don't know if that changes
the tolerance for multi-database model support.

Are you sure the view wasn't doctored with the same technique?

- V

[MichelleBoivin]

Nope. I just reran the query on a machine that still has ArcGIS Desktop 10.0 SP5 and ArcSDE 10.0 SP5. Ran the following query using two different databases (one spatial (WATER - SDE), one not (GIS_REPOSITORY_TABLES - non-spatial) and was able to create the spatial view. In addition, I can also create a spatial view using 10.1 SDE SP1 command line back to a 10.0 SDE database. I cannot use 10.0 SDE SP5 command line (or toolboxes) back to a 10.1 SDE database.

O:\>sdetable -o create_view -T PLN_DEVELOP_FEE_AREAS_VIEW_2 -t wtr.PLN_DEVELOP_F
EE_AREAS,GIS_Repository_Tables.dbo.FFCIMPORT -c wtr.PLN_DEVELOP_FEE_AREAS.Shape,
wtr.PLN_DEVELOP_FEE_AREAS.IDNumber,GIS_Repository_Tables.dbo.FFCIMPORT.IDNum,GIS
_Repository_Tables.dbo.FFCIMPORT.ProjectNum,wtr.PLN_DEVELOP_FEE_AREAS.ObjectID -
w "wtr.PLN_DEVELOP_FEE_AREAS.IDNumber=GIS_Repository_Tables.dbo.FFCImport.IDNum"
-i sde:sqlserver:GS005 -s GS005 -D WATER


ArcSDE 10.0  for SQL Server Build 685 Fri May 14 12:05:43  2010
Attribute        Administration Utility
-----------------------------------------------------
Successfully created view PLN_DEVELOP_FEE_AREAS_VIEW_2.

[MichelleBoivin · Accepted solution]

So the overall answer to this ended up being the following (below) per an ESRI Incident I entered. I tested it and it works, however if you have numerous views to create and maintain on a regular basis, this is definitely NOT the best practice. In addition, ESRI does not fully support this process in that they do not support modifications on the back-end. Please keep in mind that this is for data stored in SDEBINARY. If your data is stored in the GEOMETRY type, you can use the DatabaseView tool in 10.1 to create a cross-database spatial view.

1. Import a copy of the SQL table in question into the ArcSDE database as the owner of the data.

2. Create a view on this table in the ArcSDE database using the following sde command syntax:
sdetable -o create_view -T -t -c -w -i -s -D -u -p

For example:
sdetable -o create_view -T PLN_DEVELOP_FEE_AREAS_VIEW -t PLN_DEVELOP_FEE_AREAS,FFCIMPORT -c PLN_DEVELOP_FEE_AREAS.Shape,PLN_DEVELOP_FEE_AREAS.IDNumber,FFCIMPORT.IDNum,FFCIMPORT.ProjectNum,PLN_DEVELOP_FEE_AREAS.ObjectID -w "FFCImport.IDNumber=PLN_DEVELOP_FEE_AREAS.IDNum" -i sde:sqlserver:<server> -s <server> -D WATER

3. Grant the ArcSDE table and view owner permissions to the original non-ArcSDE database and table.

4. Edit the view design within SQL server to reference the original table stored in the non-ArcSDE database.

5. Drop the copied table within the ArcSDE database. The view should now be pointing to the original table located within the non-ArcSDE database.

Verify all necessary permissions have been granted to the table within the non-ArcSDE database in order to see the contents of the view within ArcCatalog.

Hope this helps some of you out there.......

~Michelle