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
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.
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).