Make Query Layer through joining two tables?

[YangLiu2]

Hi,

I try to create a query layer through joining two tables(table1 and table2) from different databases (database1 and database2) located in different SQL servers(server A and server B).

These two servers are located in different computers. The server A is main one and the server B is linked server contacting with server A. Besides, I have two accounts, M-ADMIN and M. The M-ADMIN could login to the server A. In addition, the ArcMap is installed on third computer which M and M-ADMIN both can login to. On server A, M-ADMIN and M are set up on same permission. Also, on ArcCatalog, both account can create database connection of MyDatabase in server A. When I open ArcMap as M-ADMIN, I can create a query layer and add to mxd; however, when I open ArcMap as M, I cannot create same query layer. An error window pops up saying "Underlying DBMS error". Does anyone have idea why? Thanks!

The query statement is as following"

SELECT  a.CountyID, a.FIPS, a.County, a.Status, dbo.table1.Shape 
FROM         [server B].database2.dbo.table2 AS a INNER JOIN 
                 dbo.table1 ON a.FIPS = dbo.table1.FIPS

-YL

[VinceAngelo]

I doubt cross-database queries are supported.  Cross-server queries with different authentication
domains are definitely not supported.

The best way to accomplish this is to use Microsoft tools to generate a view that correctly executes
your desired query.  Accessing the view would then only incur the frightful performance cost for
such a join.

- V

[YangLiu2]

I doubt cross-database queries are supported.  Cross-server queries with different authentication
domains are definitely not supported.

The best way to accomplish this is to use Microsoft tools to generate a view that correctly executes
your desired query.  Accessing the view would then only incur the frightful performance cost for
such a join.

- V

Hi V,

Thanks for quick response. What do you mean that "use Microsoft tools to generate a view"? I create a view in SQL Server. But the view with geometry column couldn't be seen on ArcMap. Thanks!

-YL

[VinceAngelo]

All views must be conformant to ArcGIS requirements:
1) Only one geometry column
2) Only one geometry type (point/line/poly) in return rows (same type or nil or NULL for all rows)
3) A registered rowid column must exist -
  a) INTEGER type (32-bit)
  b) Defined NOT NULL
  c) All values must be unique
  d) Rowid values must be repeatable on re-query (no rownum)

When joining with a feature class, the rowid column of the business table can generally
be used (unless the join results in many rows with the same shape).

- V

[YangLiu2]

All views must be conformant to ArcGIS requirements:
1) Only one geometry column
2) Only one geometry type (point/line/poly) in return rows (same type or nil or NULL for all rows)
3) A registered rowid column must exist -
  a) INTEGER type (32-bit)
  b) Defined NOT NULL
  c) All values must be unique
  d) Rowid values must be repeatable on re-query (no rownum)

When joining with a feature class, the rowid column of the business table can generally
be used (unless the join results in many rows with the same shape).

- V

Good morning V,

I create a view in SSMS. This view meets all rules as you listed. The view is created through cross-instance query. Its columns include CountyID, FIPS, CountyName, Status, and Shape(which is geometry type and is polygon). The CountyID colume is unique column. When I open ArcMap and drap the view to ArcMap from geodatabase A, a 'Warning' window pops up saying "Could not add the specified data object to the map. Underlying DBMS error" again. I read a Thread posted 2 years ago. Some one says ArcGIS 10.0 and later version can support cross-instance query layer. Do you think the error occurs because of user permission setup? Thanks!

Best,
-YL

[VinceAngelo]

You would need to capture the exact error (as reported by the database) before I could
render any judgement on the cause of the error (random guesses just waste time).
Tech Support could help you find both the ArcGIS and database logs.

- V