SQL Error Shape.STContains is not allowed

Question

I am trying to create a SQL view to count the number of point features in a polygon.  The following code works when run in a SQL Query, however when I try to create a view using the code I get "Shape.STContains is not allowed, and the column name dbo could not be found or is ambiguous"

SELECT POLYGON.NAME, Count(*)
from Point JOIN
POLYGON
On POLYGON.Shape.STContains(POINT.SHAPE) = 1
GROUP BY POLYGON.NAME

Any assistance would be appreciated.

MartinRowe1 · Answer

down voteacceptYAAYYYYYYY. Got it.

It seems that the STContains function won't operate with a DBO schema in front of the field name.

If you add the database name to the query it works. Should look like this.

USE MyDatabase

Select dbo.PointTab.*, dbo.PolyTab.Name from dbo.PointTab

Inner Join dbo.PolyTab

On PolyTab.GeometryCol.STContains(dbo.PointTab.GeometryCol) = 1

MartinRowe1 · Answer

Please tell me you managed to solve this one?

Then immediately thereafter tell me how you did it.

Have reinstalled servers and .net systems and this one has baffled me.

Thanks hugely in advance

Martin

DionO_Reilly · Answer

Creating in SQL management studio. It displays correct results in SQL query but when try to make the view it throws that error