SQL Server 2017 CLR Error: Could Not Find type in assembly

Question

I'm creating a CLR stored procedure in SQL Server 2017. I've done this many times in previous versions without any issue; however, we have recently updated to SQL Server 2017.

I'm aware of the security issues this upgrade presents, but I wanted to make sure I could deploy my CLR before I started messing with security. I created a database called Test and set Trustworthy to ON (bad I know -- temporary). I keep getting the following error:

Could not find Type 'CLR_Get_API_Data.StoredProcedures' in assembly 'Get_API_Data'.

I found several articles e.g.: SQL Server: Could not find type in the assembly that dealt with this error, but they built their code in a class and not a partial class of type storedProcedures.

This is my SQL Script to create the stored procedure:

USE Test 
GO

--Alter Database Test
--Set Trustworthy on

--EXEC sp_configure 'clr strict security', 1;
--RECONFIGURE;

IF EXISTS (SELECT * FROM sys.assemblies asms 
           WHERE asms.name = N'Get_API_Data' AND is_user_defined = 1)
    DROP ASSEMBLY [Get_API_Data]
GO

CREATE ASSEMBLY Get_API_Data
FROM 'C:\Users\Administrator\Documents\Visual Studio 2015\Projects\CLR_Assemblies\Get_API_Data\Get_API_Data\bin\Debug\Get_API_Data.dll'
WITH Permission_Set = Safe --EXTERNAL_ACCESS 
GO

--Assembly Name,[SolutionName.StoredProcedures].Sub Name
CREATE PROCEDURE [dbo].[GetAPI]
AS EXTERNAL NAME Get_API_Data.[CLR_Get_AMS_API_Data.StoredProcedures].GetAPI
GO

My solution name is CLR_Get_API_Data and my project name is Get_API_Data and my method name is GetAPI. The following is my Visual Studio code:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Net;
using System.IO;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void GetAPI ()
    {
         // Stuff happens here
    }    //Ends Public Void Get
}    //Ends Class

Answer 1

Nothing wrong with using a partial class.

The CLR_Get_AMS_API_Data portion of [CLR_Get_AMS_API_Data.StoredProcedures] refers to a namespace name. Your code does not show that you are using a namespace. Remove the CLR_Get_AMS_API_Data. so that you are left with:

AS EXTERNAL NAME Get_API_Data.[StoredProcedures].GetAPI

---

For more info on properly / securely deploying SQLCLR projects in SQL Server 2017 and newer, please see my posts:

1. SQLCLR vs. SQL Server 2017, Part 2: “CLR strict security” – Solution 1 — more steps than Part 3, Solution 2 (below), but a good fit for existing projects as it requires almost no changes to the existing solution or even deployment process (and in fact, this is effectively the route that I went for my SQL# project as all it did was add 3 simple steps to the beginning of the installation script)
2. SQLCLR vs. SQL Server 2017, Part 3: “CLR strict security” – Solution 2

For more info on working with SQLCLR in general, please visit: SQLCLR Info