How to import Microsoft.SqlServer.Types into Microsoft SQL Server 2012?

Question

I just finished importing census block shape files into Microsoft SQL Server 2012 and am now having issues when trying to use some of the geography features (STContains, STWithin, UnionAggregate, etc) on the data I've brought in. I checked the .prj file before importing my .shp files and I'm sure it is geogrpahy and not geometry type.

This is the example I've been trying, just to test it out (which comes straight from the MSDN website):

DECLARE @g geography;
DECLARE @h geography;
SET @g = geography::Parse('CURVEPOLYGON (COMPOUNDCURVE (CIRCULARSTRING (-122.200928    47.454094, -122.810669 47.00648, -122.942505 46.687131, -121.14624 45.786679, -119.119263  46.183634), (-119.119263 46.183634, -119.273071 47.107523, -120.640869 47.569114, -122.200928 47.454094)))');
SET @h = geography::Parse('POINT(-121.703796 46.893985)');

select @g.stcontains(@h)

This is the error I receive:

Msg 6506, Level 16, State 10, Line 6
Could not find method 'stcontains' for type 'Microsoft.SqlServer.Types.SqlGeography' in  assembly 'Microsoft.SqlServer.Types'

I have done some research on the subject and it appears as though I need to install some sort of add on feature. I checked my C: folder because I saw a recommendation to install it via Program Files/Microsoft SQL Server/100/SDK/Assemblies/Microsoft.SqlServer.Types.dll but got stumped because I couldn't find an 'Assemblies' folder. I also saw a recommendation to download the Microsoft SQL Server 2012 feature pack (http://www.microsoft.com/en-us/download/details.aspx?id=29065) but I wasn't sure what exactly I needed, if that was even the right spot to look.

Any help that you may offer would be greatly appreciated. Thanks in advance.

Answer 1

For a lot of my projects I create a folder at the project level (Visual Studio) called "libraries" where I put DLL's of this nature and other third party stuff. I don't know if that's standard practice, but everyone I work with has done this for a while and it's worked well in TFS and Subversion before that.

Anyway, mine is sitting in this directory (SQL 2012 / Win 7 / 64 bit OS):

C:\Program Files (x86)\Microsoft SQL Server\110\Shared

I copy it from that directory to the library folder in my project.

Answer 2

The method name is case sensitive and expects STContains rather than stcontains

DECLARE @g geography;
DECLARE @h geography;
SET @g = geography::Parse('CURVEPOLYGON (COMPOUNDCURVE (CIRCULARSTRING (-122.200928    47.454094, -122.810669 47.00648, -122.942505 46.687131, -121.14624 45.786679, -119.119263  46.183634), (-119.119263 46.183634, -119.273071 47.107523, -120.640869 47.569114, -122.200928 47.454094)))');
SET @h = geography::Parse('POINT(-121.703796 46.893985)');

select @g.STContains(@h)

Answer 3

I've encountered the same issue working with SQL Server 2016 LocalDB:

Could not find method 'STCrosses' for type 'Microsoft.SqlServer.Types.SqlGeography' in assembly 'Microsoft.SqlServer.Types'

It took me a while to understand that STCrosses is for geometry data type, while for geography I should use STIntersects.