SQL Server: Could not find type in the assembly

Question

Assume the assembly dll:

using Microsoft.SqlServer.Server;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System;
using System.Text;

namespace CLRFunctions
{
    public class T
    {
        [SqlFunction(DataAccess = DataAccessKind.Read)]
        public static String NormalizeString(String s, String normalizationForm)
        {
            NormalizationForm form = NormalizationForm.FormC;

            if (String.Equals(f, "FormD", StringComparison.OrdinalIgnoreCase))
                form = NormalizationForm.FormD;

            return s.Normalize(form);
        }
    }
}

Note: Target the assembly to .NET 3.5 as SQL Server doesn't support .NET 4.0

Copy the assembly to a location, and "creating" the assembly works fine:

CREATE ASSEMBLY CLRFunctions FROM 'c:\Program Files\My App\CLRFunctions.dll';

Note: And then enable CLR functions, otherwise they are broken by default:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

Created the user-defined function fails:

CREATE FUNCTION NormalizeString(@s nvarchar(max), @normalizationForm varchar(50)) 
RETURNS nvarchar(max)
AS EXTERNAL NAME CLRFunctions.T.NormalizeString

fails with error:

Msg 6505, Level 16, State 2, Procedure NormalizeString, Line 1
Could not find Type 'T' in assembly 'CLRFunctions'.

Why can SQL Server not find type T in assembly CLRFunctions?

enter image description here

Note: Why T? Cause Microsoft did.

Martin Smith · Accepted Answer · 2011-10-22 22:21:49Z

105

Try

CREATE FUNCTION NormalizeString(@s nvarchar(max), 
                                @normalizationForm nvarchar(50)) 
RETURNS nvarchar(max)
AS EXTERNAL NAME CLRFunctions.[CLRFunctions.T].NormalizeString

answered Oct 22, 2011 at 22:21

Martin Smith

457k97 gold badges777 silver badges886 bronze badges

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

3 Comments

Jarrod Dixon Over a year ago

Great answer - this was causing me to scream obscenities at SSMS.

Leblanc Meneses Over a year ago

in vs 2010 the default template doesn't put namespace by default and hence my fully qualified class name was incorrect. notation is: RegisteredAssemblyName.[FullyQualifiedClassName].FunctionName

QueueHammer Over a year ago

For those reading this who are exhausted or aggravated from working with MSSQL; Notice inside the brackets... That is "the namespace" "dot" "the class name" or as @Lemblanc correctly calls it the "FullyQualifiedClassName"

Ian Boyd · Accepted Answer · 2019-02-05 21:15:55Z

4

I just busted my skull on this in Visual Studio 2017, building a CLR in VB. What I found out, when creating the procedure in SQL, THE EXTERNAL NAME is set as follows:

AssemblyName.[Assemblyname.ClassNameInVBProgram].SubroutineNameInVBProgram

And it is Case Sensitive.

Use Create Assembly in SQL to create the Sql Assembly

Use Create Procedure in SQL to create the CLR SP.

edited Feb 5, 2019 at 21:15

Ian Boyd

259k271 gold badges920 silver badges1.3k bronze badges

answered Feb 5, 2019 at 21:08

Sonny

411 bronze badge

2 Comments

CB_Ron Over a year ago

Works great if you're creating the assembly from a file. But Azure SQL Database and Managed Instance do not allow creating assemblies from a file. You have to create it from a varbinary. Seems simple enough, except somehow it breaks the syntax of the EXTERNAL NAME clause. The exact same syntax that works from file in my on-prem SQL Server 2008 R2 fails when creating from varbinary in both the on-prem and Managed Instance (SQL 2017) databases.

Aashishkebab Mar 19 at 23:40

Incorrect. It uses the namespace, not the assembly name, as a qualifier.

Collectives™ on Stack Overflow

SQL Server: Could not find type in the assembly

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related