Create Assembly is successful, nothing found in sys.assembly_modules

Question

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?

Answer 1

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.