Implementing a SQL Server 2008 User-defined function in managed code for geocoding

Question

The goal:
To create a .NET dll i can reference from inside SQL Server where i can pass in an address & get back a geocode string that i can stick into a geography data type as a POINT using STPointFromText() in t-SQL.

I'm using Virtual Earth, I signed up for a developer account which gave me access to virtual earth staging servers.

I'm trying to create a user defined function in a similar way to the example shown here although using Visual Studio 2008 of course. I have code that I've written in ASP.NET that uses virtual earth to pass in an address & get back the latitude/logditude co-ordinates.

The problem:
The only problem is that virtual earth requires you to 'add a service reference' for the geocode service and for some reason when i go to the project menu there's Add Reference and Add Web Reference, but no Add Service Reference which is what i need to be able to call geocodeService.Geocode(geocodeRequest) which does all the magic.

Can someone tell me what am i doing wrong? why is it not possible to add a service reference to a SQL Server project in visual studio?

Answer 1

Right click on the Sql Server Project, go to properties, make sure the target framework is set to .Net 3.5. My guess is its targetting the .Net 2.0 FW.

if you do need to stick to the 2.0 Framework, open the .svc link in a browser and extract the wsdl url, you can do an "Add Web Reference" instead