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.

score 94 · Accepted Answer · answered Oct 22 '11 at 22:21

94

Try

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

answered Oct 22 '11 at 22:21

Martin Smith

402,107
79
682
775

5

Great answer - this was causing me to scream obscenities at SSMS. – Jarrod Dixon Feb 26 '12 at 09:51
5

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 – Leblanc Meneses Dec 15 '12 at 07:05
Thanks your answer helped me. – Muhammad Ummar Apr 16 '13 at 07:31
13

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" – QueueHammer Sep 04 '13 at 22:00

score 2 · Answer 2 · edited Feb 05 '19 at 21:15

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.

edited Feb 05 '19 at 21:15

Ian Boyd

220,884
228
805
1,125

answered Feb 05 '19 at 21:08

Sonny

21
1

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. – CB_Ron Mar 01 '19 at 00:17

SQL Server: Could not find type in the assembly

2 Answers2

Linked