I am trying to use an assembly called from a function in SQL Server 2005,

Here is the assembly code:
Public Class Distance

Public Function getDistance(ByVal lat1 As Double, ByVal lon1 As Double, ByVal lat2 As Double, ByVal lon2 As Double) As Double
Dim theta As Double
Dim dist As Double
Dim distance As Double
theta = lon1 - lon2
dist = Math.Sin(CDbl(deg2rad(lat1))) * Math.Sin(CDbl(deg2rad(lat2))) + Math.Cos(CDbl(deg2rad(lat1))) * Math.Cos(CDbl(deg2rad(lat2))) * Math.Cos(CDbl(deg2rad(theta)))
dist = Math.Acos(dist)
dist = CDbl(rad2deg(dist))
distance = dist * 60 * 1.1515
Return distance
End Function

Private Function deg2rad(ByVal Deg As Double) As Double
Return CDbl(Deg * Math.PI / 180)
End Function

Private Function rad2deg(ByVal Rad As Double) As Double
Return CDbl(Rad * 180 / Math.PI)
End Function

End Class

Here is the code to register the assembly with sql server:
CREATE ASSEMBLY asmDistanceFunctions
FROM 'E:\web\clients\out2daylive\Bin\asmDistanceFunctions.dll'
WITH PERMISSION_SET = SAFE
GO

Here is the code for the function calling the assembly - THIS IS WHERE I GET AN ERROR!
CREATE FUNCTION dbo.clrComputeDistance
(
@lat1 as float,
@lon1 as float,
@lat2 as float,
@lon2 as float
)
RETURNS float
AS EXTERNAL NAME [asmDistanceFunctions].[asmDistanceFunctions.Distance].[getDistance]
GO

I get the following error:
Msg 6573, Level 16, State 1, Procedure clrComputeDistance, Line 1
Method, property or field 'getDistance' of class 'asmDistanceFunctions.Distance' in assembly 'asmDistanceFunctions' is not static.

Guy Hengel [angelIII / a3]Billing EngineerCommented:

Public Class Distance

Public Static Function getDistance(ByVal lat1 As Double, ByVal lon1 As Double, ByVal lat2 As Double, ByVal lon2 As Double) As Double
Dim theta As Double
Dim dist As Double
Dim distance As Double
theta = lon1 - lon2
dist = Math.Sin(CDbl(deg2rad(lat1))) * Math.Sin(CDbl(deg2rad(lat2))) + Math.Cos(CDbl(deg2rad(lat1))) * Math.Cos(CDbl(deg2rad(lat2))) * Math.Cos(CDbl(deg2rad(theta)))
dist = Math.Acos(dist)
dist = CDbl(rad2deg(dist))
distance = dist * 60 * 1.1515
Return distance
End Function

Private Function deg2rad(ByVal Deg As Double) As Double
Return CDbl(Deg * Math.PI / 180)
End Function

Private Function rad2deg(ByVal Rad As Double) As Double
Return CDbl(Rad * 180 / Math.PI)
End Function

Of course, you could always also do this as a regular T-SQL function as well and not have the overhead of having to load an assembly (returns distance in meters):

actually doing it the way I am trying to will be several times faster. right now it takes more than a second to run my query - that's just 1 user with no one else hitting the database. From what I have read this should reallypeed this up for me since I am using SQl Server 2005 has asp.net CLR hosted in it.

0

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

I figured it out, I needed to add shared to all of my functions:

Public Class Distance

Public Shared Function getDistance(ByVal lat1 As Double, ByVal lon1 As Double, ByVal lat2 As Double, ByVal lon2 As Double) As Double
Dim theta As Double
Dim dist As Double
Dim distance As Double
theta = lon1 - lon2
dist = Math.Sin(CDbl(deg2rad(lat1))) * Math.Sin(CDbl(deg2rad(lat2))) + Math.Cos(CDbl(deg2rad(lat1))) * Math.Cos(CDbl(deg2rad(lat2))) * Math.Cos(CDbl(deg2rad(theta)))
dist = Math.Acos(dist)
dist = CDbl(rad2deg(dist))
distance = dist * 60 * 1.1515
Return distance
End Function

Private Shared Function deg2rad(ByVal Deg As Double) As Double
Return CDbl(Deg * Math.PI / 180)
End Function

Private Shared Function rad2deg(ByVal Rad As Double) As Double
Return CDbl(Rad * 180 / Math.PI)
End Function

benchmarks on selecting variable long lat and a calculating distance from a defined position for 100,000 rows from a spatial database (OK, I'm doing a clustered index scan, with top 100,000 so this is pretty straightforward and provides a like for like comparison).

with CLR:
436 ms

with T-SQL UDF
548 ms

I eliminated the FIRST execution of the CLR process, as this had a small overhead in loading into memory for the first time (negligible, really) but wanted to make it accurate.

Then upped to 1,000,000 rows. CLR=8.2 seconds, T-SQL = 11.3 seconds.

In the long run the CLR does appear to be faster. But only significant at high volumes.

CLR comes with the extra overhead of requiring the .NET framework and SQL 2005 - T-SQL will work fine on SQL 2000 (depending on your requirements this may be fine for you). However, there are documented instances of SQL not freeing up the resources when you want to deploy a new version of the assembly - you DON'T want to have to restart a production SQL Server simply to deploy.

Also, when mirroring (or log shipping or using peer-to-peer replication) the embedded assemblies are not mirrored - only the data and schema. This places an administrative overhead (documentation on change control) - again, this may not be a concern for you - just want to make sure you are aware of it.

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Public Static Function getDistance(ByVal lat1 As Double, ByVal lon1 As Double, ByVal lat2 As Double, ByVal lon2 As Double) As Double

Dim theta As Double

Dim dist As Double

Dim distance As Double

theta = lon1 - lon2

dist = Math.Sin(CDbl(deg2rad(lat1

dist = Math.Acos(dist)

dist = CDbl(rad2deg(dist))

distance = dist * 60 * 1.1515

Return distance

End Function

Private Function deg2rad(ByVal Deg As Double) As Double

Return CDbl(Deg * Math.PI / 180)

End Function

Private Function rad2deg(ByVal Rad As Double) As Double

Return CDbl(Rad * 180 / Math.PI)

End Function

End Class