How to create a view on Arc-catalog using tables from two different databases residing on different servers

IreneEgbulefu1 — Mon, 12 Mar 2018 15:26:50 GMT

I have some features (Road, Drainage and Parcels - Line, Point and Polygon) data. The spatial attributes of these features are captured by drawing them into arcmap and then stored feature class in a feature dataset in a geodatabase that is on MSSQL2012 database on Server A. The non spatial attributes of these features are existing on another DEVSQL2012 on Server B.

I am able to connect to the different databases using ArcCatalog Database connection and so able to view both the spatial and non-spatial data separately.

I am now trying to create a database View from my ArcCatalog to join the spatial table on Server A to the non-spatial table on Server B. But I keep getting various errors.

Please does anyone has an idea how I can get this resolved.

SELECT [DEVSQL2012].TVR_CIPS.dbo.Projects.Id,

TVR_CIPSdboProjectsName

TVR_CIPS.dbo.Projects.Name,

TVR_CIPS.dbo.Projects.ProjectTypeId,

TVR_CIPS.dbo.Projects.ProjectManagerId,

TVR_CIPS.dbo.Projects.IsFunded,

TVR_CIPS.dbo.Projects.Scope,

TVR_CIPS.dbo.Projects.FundSource,

TVR_CIPS.dbo.Projects.TotalEstimatedCost,

TVR_CIPS.dbo.Projects.Consultant,

TVR_CIPS.dbo.Projects.Contractor,

TVR_CIPS.dbo.Projects.LocationLimits,

TVR_CIPS.dbo.Projects.GISId,

TVR_CIPS.dbo.Projects.StatusId,

TVR_CIPS.dbo.Projects.PermalinkMapURL,

TVR_CIPS.dbo.Projects.CIPDocURL

FROM TVR_CIPS.dbo.Projects INNER JOIN

[Frussmolt\TVR3PRD02].PublicWorks.TC_USER.CIP_Point_Only

[DEVSQL2012].TVR_CIPS.dbo.Id = [Frussmolt\TVR3PRD02].PublicWorks.TC_USER.CIP_Point_Only.Project_ID

Here is the error I'm getting

I don't understand why it is seeing that column as invalid. the column is supposed to be a Yes or No (0 and 1) though the field datatype for this column is 'Short integer', Could this be a problem?

Re: How to create a view on Arc-catalog using tables from two different databases residing on different servers

Asrujit_SenGupta — Mon, 12 Mar 2018 15:40:07 GMT

Try creating a cross database view, using a Linked Server Connection, from the Database end and then add it in ArcMap...see if that works

topic How to create a view on Arc-catalog using tables from two different databases residing on different servers in ArcGIS Enterprise Questions

How to create a view on Arc-catalog using tables from two different databases residing on different servers

Re: How to create a view on Arc-catalog using tables from two different databases residing on different servers