AnsweredAssumed Answered

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

Question asked by EgbuleI on Mar 12, 2018
Latest reply on Mar 12, 2018 by asrujit_pb

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

ON

[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?

Outcomes