1

I have some features (Roads, Drainages 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

enter image description here

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

  • 2
    You cannot join tables from different databases on different servers.You will need to register table from server B in database on server A using a DB link. Once both are in the same database (or server) you could create the view. Optionally (and more simple), use layer and table views to create an in memory join. This is not at the SQL level but at the application level. Please modify your question once you decide what approach you want to take. – Ben S Nadler Mar 12 '18 at 18:00
  • 1
    It's unwise to attempt joins across databases, much less servers. The canonical solution is to replicate the data, then use it in place. – Vince Mar 12 '18 at 22:27
  • A join in ArcGIS will not be dynamic, unlike a Query Layer. If the data is dynamic, a DB link is the best option. DB links are actually for Oracle databases, but I think the same mechanism exists for SQL. – GISGe Mar 13 '18 at 9:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.