Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Hopefully the question is fairly self-explanatory.

I've been ferreting through the, what appears to be quite minimal/poor microsoft documentation regarding both views and geography datatypes in the sql server 2008, and I think the answer is "no, geography datatypes are either not supported or minimally supported within a view"; but I'm not entirely sure, and I'd love some corroboration.

I've been attempting to add geography data to an existing view. And my end goal is to retrieve longitude/latitude data from the field in question.

Here's the select statement (simplified) that works when applied to the table but doesn't work when defined within the view:

SELECT     
    [dbo.vendor.location].Lat AS latitude,
FROM         
    dbo.vendor

Note that [dbo.vendor.location] is the field, and Lat is the latitude property of the geography datatype.

And when that sql is run within the view, the following error is returned:

The multi-part identifier "dbo.vendor.location.Lat" could not be bound

Edit:

Note that I've also tried the following query, with similar results:

SELECT     
    [dbo].[vendor].[location].Lat AS latitude,
FROM         
    dbo.vendor

In this case, SQL Management Studio removes the quotes and returns the same "multi-part ... could not be bound" error.

share|improve this question
    
Drop the quotes on your column name. The way it's written, you're telling SQL Server to look for a single object/column named "dbo.vendor.location", and not a multi-part name (schema.table.column). Quote them individually if you must. ([dbo].[vendor].[location]) –  db2 Dec 24 '13 at 20:20
    
Oh, yes, I might have mentioned that I tried that, with similar results. I'll add it in an edit. –  David Dec 24 '13 at 20:27
    
There is no way that that SQL query works "when applied to the table". And Views in SQL Server support just about anything that you can do with a valid data-SELECT statement, excepting the ORDER BY clause. –  RBarryYoung Dec 25 '13 at 18:25

1 Answer 1

up vote 3 down vote accepted

Don't reference it as [dbo].[vendor].[location].Lat in the SELECT list.

use simply vendor.location.Lat or location.Lat or define a table alias and use that.

CREATE TABLE dbo.Vendor([location] geography)

GO

/*Works fine*/
CREATE VIEW V1
AS
SELECT     
   v.location.Lat AS latitude
FROM         
    dbo.vendor v

GO

/*Fails*/
CREATE VIEW V2
AS
SELECT     
   dbo.vendor.location.Lat AS latitude
FROM         
    dbo.vendor

The use of the column dbo.vendor.location on its own (without the CLR property call) does in fact still work in the column list but the use of "More than two-part column name" is on the list of deprecated TSQL features anyway.

share|improve this answer
    
Thanks Martin! Yep, the existing view used the dbo... notation in all the column selections, so being the SS2008 newb that I am, I assumed it would work for this case as well. Glad the answer was so simple! –  David Dec 26 '13 at 15:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.