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?

Note: Why T? Cause Microsoft did.

Solution 1

Try

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

Solution 2

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.

Comments

[Jarrod Dixon]

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

[Leblanc Meneses]

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].FunctionNam‌​e

[Muhammad Ummar]

Thanks your answer helped me.

[QueueHammer]

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"

[CB_Ron]

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.