0

I have a regular SQL Server view that refers to the planar spatial data type, geometry. When I import this database view into my SSDT project, it throws the following exception:

SQL70561: Cannot schema bind view 'MyView' because name 'geometry' is invalid for schema binding. Names must be in a two-part format and an object cannot reference itself.

It seems like the type geometry is not recognized by my SQL Server Database Project in Visual Studio. My target SQL Server version is 2016.

Here's the Visual Studio screenshot:

enter image description here

TIA

Mukesh Kumar
  • 656
  • 5
  • 26
  • When I try to create an [MVP](https://stackoverflow.com/help/minimal-reproducible-example), I don't get that issue. I tried `create table dbo.GeoTable (Id int, geo geometry); create view dbo.GeoView with schemabinding as select Id, geo from dbo.GeoTable` and was able to build the project with no problems. What does your code look like? – Ben Thul Feb 16 '23 at 21:51
  • Thanks for your message. I updated the post with the screenshot of the code and the error. Please take a look at it. Also, just so you know, it works perfectly fine if I run it on SQL Server through management studio. – Mukesh Kumar Feb 17 '23 at 02:04
  • Ah... I see. What are you actually trying to do? Not from a technical perspective, but from a functional one. That is, it looks like you have string data that you're trying to convert to geometry points (though with names like 'latitude', 'longitude', I might suggest that the geography data type is a better fit). Is doing that in the service of being able to run spatial queries? Would a computed column on the source table work for that purpose? In the spirit of an MVP, please post code (not pictures of it) of the schema definitions for the table and view. – Ben Thul Feb 17 '23 at 14:44
  • Thanks for your help, Ben. I couldn't share the original code publically but wanted to give an idea. It was just an exemplary code that I managed to create and take a picture of. However, I understand your point from the MVP standpoint. Nevertheless, I managed to fix it and posted the answer. Once again, thanks for your patience and help. – Mukesh Kumar Feb 20 '23 at 03:27

1 Answers1

0

It took me two days to figure it out. So, whenever we have a view containing the clause WITH SCHEMABINDING it is forced to bind the schema for everything included in the PL/SQL block. In my case, it was trying to look under dbo and couldn't find geometry there. So, once I specified system schema sys in front of the geometry, it was accepted.

Hopefully, it helps someone in the future.

enter image description here

Mukesh Kumar
  • 656
  • 5
  • 26