SQL Server can't find my function in my DLL / Assembly

Question

I created a library class in C# with this code as you can see:

namespace ManagedCodeAndSQLServer
{
    public class BaseFunctionClass
    {
        public BaseFunctionClass()
        {
        }


        [SqlProcedure]
        public static void GetMessage(SqlString strName, out SqlString
        strMessge)
        {
            strMessge = "Welcome," + strName + ", " + "your code is getting executed under CLR !";

        }
    }
}

I built this project with the UNSAFE Permission Set property, and I added the DLL to SQL Server using this code:

use master;
grant external access assembly to [sa];
use SampleCLR;
CREATE ASSEMBLY ManagedCodeAndSQLServer
AUTHORIZATION dbo
FROM 'd:\ManagedCodeAndSQLServer.dll'
WITH PERMISSION_SET = UNSAFE
GO

It added the assembly as part of my database.

I want to call the function as you can see:

CREATE PROCEDURE usp_UseHelloDotNetAssembly
@name nvarchar(200),
@msg nvarchar(MAX)OUTPUT
AS EXTERNAL NAME ManagedCodeAndSQLServer.[ManagedCodeAndSQLServer.
BaseFunctionClass].GetMessage
GO

But I get this error:

Msg 6505, Level 16, State 2, Procedure usp_UseHelloDotNetAssembly, Line 1
Could not find Type 'ManagedCodeAndSQLServer.
BaseFunctionClass' in assembly 'ManagedCodeAndSQLServer'.

Answer 1

The problem is subtle, and in an online format such as here, it even appears to simply be a matter of formatting. But the issue is entirely found here, within the square brackets:

AS EXTERNAL NAME ManagedCodeAndSQLServer.[ManagedCodeAndSQLServer.
BaseFunctionClass].GetMessage

There is an actual newline character after ManagedCodeAndSQLServer. that should not be there. It is even reflected in the error message:

Msg 6505, Level 16, State 2, Procedure usp_UseHelloDotNetAssembly, Line 1
Could not find Type 'ManagedCodeAndSQLServer.
BaseFunctionClass' in assembly 'ManagedCodeAndSQLServer'.

which again looks like a matter of word-
wrapping ;-), but isn't. If the newline is removed such that the T-SQL appears as follows:

AS EXTERNAL NAME ManagedCodeAndSQLServer.[ManagedCodeAndSQLServer.BaseFunctionClass].GetMessage;

then it will work just fine.

Some additional notes:

1. No need to ever grant sa anything. Any member of the sysadmin fixed server role already has all permissions.
2. There is no need for the code shown in the question to be marked as anything but SAFE. Please do not mark any Assembly as UNSAFE unless absolutely necessary.
3. There is no need to set the Database to TRUSTWORTHY ON. That is a security risk and should be avoided unless absolutely necessary.
4. If you are learning SQLCLR, please see the series I am writing on that topic on SQL Server Central: Stairway to SQLCLR (free registration is required to read their content, but it's worth it :-).