2

I have two basic tables with geometry column types in them. One has polygons and the other points, and I am trying to join them using the following basic query:

Select dbo.PointTab.*, dbo.PolyTab.Name from dbo.PointTab
Inner Join dbo.PolyTab
On
dbo.PolyTab.GeometryCol.STContains(dbo.PointTab.GeometryCol) = 1

I have tried a great many variations on this query, but always get the error:

Error Source:   .Net SQLClient Data Provider
Error Message:  Remote function reference
            'dbo.PolyTab.GeometryCol.STContains' is not allowed, and the
                   column name dbo could not be found or is ambiguous

I am running this on Windows Server 2016 with SQL Server Standard 2016. The .net system is 4.6, with the previous versions (3.5 and 2 enabled)

If I use ODBC to get into the database with my old faithful laptop running win 8.1 then it runs (without the DBO prefixes on the columns).

There are no useful references on the internet to this and I have run out of talent.

Thanks in advance

Martin

Martin Rowe
  • 21
  • 6
  • In addition, I have observed that if I try and add a spatial index to the column the SSMS crashes. Might be a clue?? – Martin Rowe Mar 17 '17 at 10:17
  • Hmm… that query looks just fine to me. Out of curiosity, what version of SSMS are you getting the error with? Also, the message "remote function reference" is interesting. Is one of those tables actually in another database? – Ben Thul Mar 17 '17 at 14:44
  • Hi Ben, thanks for the reply. The tables are local and I'm using SSMS 2016. Any help would be truly appreciated. The .net is 4.0.30319.42000, which I suspect is the source of the error, but I'm a geologist and not really a super database / code person. – Martin Rowe Mar 18 '17 at 07:33
  • Okay, so it will run through a stored procedure and not from a view?? Am so confused now that I doubt I could sit on a toilet the right way around. – Martin Rowe Mar 19 '17 at 09:34

1 Answers1

0

YAAYYYYYYY. 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

Martin Rowe
  • 21
  • 6