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

MichelleBoivin

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