I am currently working with some GIS data and using the Geography data type in MS SQL Server 2012. I am having some difficulty when trying to display the Latitude and Longitude of the Geography type using the View designer, specifically if I have a join with another table.
For example the below works fine in the view designer:
SELECT dbo.table1.ID, dbo.table1.LocationTypeID, dbo.table1.Location, dbo.table1.Location.Lat as LocLatitude, dbo.table1.Location.Long as LocLongitude
FROM dbo.table1
However, this does not:
SELECT dbo.table1.ID, dbo.table1.LocationTypeID, dbo.table2.LocationTypeName, dbo.table1.Location, dbo.table1.Location.Lat as LocLatitude, dbo.table1.Location.Long as LocLongitude
FROM dbo.table1 INNER JOIN
dbo.table2 ON dbo.table1.LocationTypeID = dbo.table2.ID
I found that if I remove the schema (dbo) and manually create the view via an SQL command it will create the View and run correctly, however, attempting to edit it later via the designer will display errors.
For reference the error the designer throws out is the "multi-part identifier could not be bound".
Whilst the manual creation of View can resolve the issue I would really like to find a way to solve this as it is bugging me but also causes issues for other people working on the project not knowing that now the only way to edit the View in future is to create an "Alter View" SQL command.
I have tried searching for an answer to this but have yet to find anything, normally this would not be an issue as I would just grab the Geography type object from the code and get the Lat/Lon there to be displayed but I need to create an export/View for a 3rd party.
Need to reference tables by aliases instead of directly. Answer provided by @MartinSmith