3

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

bernie2436
  • 22,841
  • 49
  • 151
  • 244
  • 1
    Could it be [this namespace issue](http://stackoverflow.com/questions/7823488/sql-server-could-not-find-type-in-the-assembly)? – Pondlife May 17 '13 at 20:33
  • Also could you use ILSpy and post a screenshot of your dll [like the question Pondlife linked to did](http://i.stack.imgur.com/TnWgn.png)? – Scott Chamberlain May 17 '13 at 21:27
  • @ScottChamberlain I will have to ask the SA to install ILSpy which will take a few days – bernie2436 May 17 '13 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 May 17 '13 at 21:35
  • @ScottChamberlain see picture – bernie2436 May 17 '13 at 21:45
  • The main thing I want to see is the tree leading up to `LevenshteinerAndSoundexer` on the left hand side. Can you expand out the + sign to make the class visible and replace the image (you don't need to include the right hand side of ILSpy)? – Scott Chamberlain May 17 '13 at 21:52
  • 4
    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](http://stackoverflow.com/q/479782/73226) – Martin Smith May 17 '13 at 21:53
  • @MartinSmith thats what I think too, was just hoping for a screenshot to confirm. – Scott Chamberlain May 17 '13 at 22:09
  • @ScottChamberlain Can you wait until Monday so I can check the new syntax before you guys close? – bernie2436 May 17 '13 at 22:51
  • also, in the picture in the dupe, they show a section for "CRL functions." My ILSpy did not show that section. Is that a problem? – bernie2436 May 17 '13 at 22:52
  • @akh2103 That's just the name of the namespace in their code. If you expand the tree view on the left until you find the class you will be able to confirm exactly the namespace yours is in. – Martin Smith May 17 '13 at 23:19

1 Answers1

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)-/
Scott Chamberlain
  • 124,994
  • 33
  • 282
  • 431