Make Query Layer through joining two tables?

Discussion created by yangliu2209 on Nov 12, 2013
Latest reply on Nov 13, 2013 by vangelo-esristaff

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