Solved

method in assembly is not static

Posted on 2006-11-04
10
1,620 Views
Last Modified: 2010-08-05
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.

How do I correct this?
0
Comment
Question by:alivemedia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17873718
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

End Class
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17873876
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):

CREATE FUNCTION DistanceBetween2PointsQ( @Lat1 float,@Long1 float, @Lat2 float, @Long2 float)
returns float as
return
    ((Abs(60 * (ACos((Sin(@Lat1 * (PI()/180)) * Sin(@Lat2 * (PI()/180)))
     + (Cos(@Lat1 * (PI()/180)) * Cos(@Lat2 * (PI()/180)) * Cos((@Long2 * (PI()/180) * -1)
     - (@Long1 * (PI()/180) * -1)))) * (180/PI())))) * 1852)

GO
declare @Lat1 float
declare @Long1 float
declare @Lat2 float
declare @Long2 float
Set @Lat1 = 48.06361
Set @Long1 = 8.4251385

Set @Lat2 = 48.063526
Set @Long2 = 8.4250002

SELECT dbo.DistanceBetween2Points(@Lat1,@Long1,@Lat2,@Long2)
0
 
LVL 2

Author Comment

by:alivemedia
ID: 17874329
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 2

Author Comment

by:alivemedia
ID: 17874334
AngelII that gives me the following error:  Methods cannot be declared static

I am sure I am missing something small here
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17874389
for visual basic you need to use Shared
0
 
LVL 2

Author Comment

by:alivemedia
ID: 17874401
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

End Class

0
 
LVL 29

Expert Comment

by:Nightman
ID: 17874407
that's what I said.

Also make sure that you have enabled clr

EXEC sp_configure 'clr enabled',1
RECONFIGURE WITH OVERRIDE
GO
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17874476
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.
0
 
LVL 29

Accepted Solution

by:
Nightman earned 500 total points
ID: 17874480
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.

Cheers
Night
0
 
LVL 2

Author Comment

by:alivemedia
ID: 17874797
thanks for the great info - definitly something to keep in mind.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question