Why can't t-sql find my function in my assembly?

Question

I am trying to create a stored procedure using the .NET CLR. I successfully compiled the following code into a .NET 3.5 DLL.

Imports System.Math
Imports System.Text.RegularExpressions
Imports Microsoft.SqlServer.Server

Partial Public Class LevenshteinerAndSoundexer

    <SqlProcedure()> _
    Public Shared Function getLevenshteinDistance(ByVal string1 As String, ByVal String2 As String) As Integer

I then successfully added it as an assembly using this code:

create assembly
LevenshteinLibrary
from 'Path\LevenshteinLibrary.dll'

But when I got to create the procedure with this code

create procedure testCLR(@s1 nvarchar(1000), @s2 nvarchar(1000))
as external NAME LevenshteinLibrary.LevenshteinerAndSoundexer.getLevenshteinDistance

I get the error

"Could not find Type 'LevenshteinLibrary' in assembly 'LevenshteinLibrary'"

Why can't it "see" thefunction?

Here is what happens when I examine the DLL in ILSpy:

enter image description here

Here is what it looks like when I expand out the library

enter image description here

Also could you use ILSpy and post a screenshot of your dll like the question Pondlife linked to did? — Scott Chamberlain
– Scott Chamberlain, Commented May 17, 2013 at 21:27
@ScottChamberlain I will have to ask the SA to install ILSpy which will take a few days — bernie2436
– bernie2436, Commented May 17, 2013 at 21:34
No, just do it on your machine, you can run it from any computer, just point it at the dll you compiled. The tool is for looking at .NET assemblies, it has nothing to do with SQL. — Scott Chamberlain
– Scott Chamberlain, Commented May 17, 2013 at 21:35
Looks same as the dupe. You need LevenshteinLibrary.[LevenshteinLibrary.LevenshteinerAndSoundexer].getLevenshteinDistance because LevenshteinerAndSoundexer is presumably in the LevenshteinLibrary namespace (as well as that being the assembly name). VB.NET automatically appends a root namespace — Martin Smith
– Martin Smith, Commented May 17, 2013 at 21:53

Scott Chamberlain · Accepted Answer · 2013-05-20 21:16:53Z

3

According to the image you uploader your class LevenshteinerAndSoundexer is under the namespace LevenshteinLibrary. Therefor you need to include the namespace of the class (inside square brackets so it does not confuse the parser) when defining which class to use:

create procedure testCLR(@s1 nvarchar(1000), @s2 nvarchar(1000))
as external NAME LevenshteinLibrary.[LevenshteinLibrary.LevenshteinerAndSoundexer].getLevenshteinDistance
--  Name of object in SQL --^                          ^    Name of function in class --^
--           Full name of class (including namespace)-/

answered May 20, 2013 at 21:16

Scott Chamberlain

128k37 gold badges299 silver badges447 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Why can't t-sql find my function in my assembly?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related