CODEWITHSUNDEEP
sql-serverintegrationclr
user212421
user212421

Reputation: 97

Create Assembly is successful, nothing found in sys.assembly_modules

First attempt at a CLR Integration, so I can use Regex tools.

I create the assembly:

CREATE ASSEMBLY SQLRegexTools
  FROM 'D:\CODE\SQLRegexTools\SQLRegexTools\bin\Release\SQLRegexTools.dll'

This succeeds, and the assembly appears in SELECT @ FROM sys.assemblies.

But there are no records returned to SELECT * FROM sys.Assembly_modules.

And when I try to CREATE FUNCTION to call one of the methods,

CREATE FUNCTION RegExMatch(@pattern varchar(max)
                            , @SearchIn varchar(max)
                            , @options int)
RETURNS varchar
 EXTERNAL NAME SQLRegexTools.Functions.RegExMatch

I get an error 'Msg 6505, Could not find Type "Functions" in assembly 'SQLRegexTools.'

The class name of the VB module is "Functions". Why is this called a type in the error, and why might I not be seeing anything in the modules?

Upvotes: 2

Views: 572

Answers (2)

user212421
user212421

Reputation: 97

That hits the answer Joey. Thank you.

There are several gotchas in this process I learned after much sleuthing and testing. They are often buried in long posts about the entire process, I will summarize here:

  • As Joey explained, the fully qualified name is needed. To be more complete...

Example: 

             1                 2                   3               4

[SQLRegexToolsASM].[SQLRegexToolsNS.RegexFunctionsClass].RegExMatch

  1. This is the assembly name in the CREATE ASSEMBLY step.
  2. This is the Root Namespace from the properties of the assembly project. If you declared a namespace or two, they must be included in their proper order, after this item and before item 3 in this list. Observe all these namespaces are enclosed in their own square brackets.

assembly.[rootnamespace.namespace.namespace].classname.methodname”

  1. This is the Class Name you assigned to the class, maybe like this

    Public Class RegexFunctionsClass

  2. The name of the method defined in your VB or C# assembly.

Upvotes: 1

Joey
Joey

Reputation: 670

This error occurs when SQL Server is unable to resolve the name as provided. Try the snippet below to see if this resolves the issue.

CREATE FUNCTION RegExMatch(@pattern varchar(max)
                            , @SearchIn varchar(max)
                            , @options int)
RETURNS varchar
AS EXTERNAL NAME SQLRegexTools.[Functions].RegExMatch

The resolution of individual functions/methods for accessing native code is relatively indistinguishable from a multipart object name (e.g. a type).

An additional note is that native code that utilizing nested namespaces must fully qualify nested namespaces within the same pair of quotes. For example, if RegExMatch were located at SQLRegexTools.A.B.C.RegExMatch, you would reference this as SQLRegexTools.[A.B.C].RegExMatch when using it with EXTERNAL NAME in SQL Server.

Upvotes: 2

Related Questions