How to execute SQLCLR UDF added directly to sqlproj SSDT and resolve SQL71501 reference error

Question

I am trying to find the answer to this simple question - but have not figured it out yet.

Say I have MyDb.sqlproj, with various sql contents (sprocs, views, trigger, etc.).

I added a new UDF via Add-> New item -> SQL CLR C #, a custom function.

For instance:

namespace MyNameSpace { public class MyClass { [SqlFunction(DataAccess = DataAccessKind.Read)] //I use different attributes here, but it doesn't matter public static int Method1() { return 0; } } } 

In the properties of MyDb.sqlproj, the SQLCLR tab MyDb is the namespace name for the assembly and the default

In my sql code, I call the clr method using EXTERNAL TITLE :

 CREATE PROCEDURE ClrMethod1 RETURNS [int] WITH EXECUTE AS CALLER AS EXTERNAL NAME [MyDb].[MyNamespace.MyClass].[Method1] 

It seems I tried everything to get the last line to compile. He cannot solve the link and get:

SQL71501: Function: [my sql function name] has an unresolved assembly reference [MyDb]

Please tell me the correct way to make it work. What can I lose?

I am using VS2010 SP1 and latest SSDT

Answer 1

You should add your compiled DLL containing your CLR code as a reference. So, in the framework of the MyDb project SSDT-> Links (right click) → Add Link, go to the DLL.

You will probably be able to access the project instead of the DLL if you had the CLR project (class library) in the same solution, but in my case I refer to the DLL (compiled for a separate solution).

Regarding the format of the string AS EXTERNAL NAME :

AS EXTERNAL NAME [AssemblyName].[ClassName].[FunctionName]

Note. . For CLR objects, I remove namespaces from my classes / code just for simplification, because this step is usually the most problematic. It's easy to confuse the name AssemblyName / DLL Name / Namespace. AssemblyName is in your CLR class library project by accessing Project Properties → Application → "Assembly Name:". I would remove any alpha-numeric / spaces from it to simplify the name and eliminate this as a problem.

So, I would try this, and once you get this working, if you really want a namespace, then you can add a namespace and figure out the syntax from there, and at least you know that the other parts are correct.

---

Good thing you have the * .cs file actually inside the same SSDT project. so in this case, if your code is this:

CS file:

 public partial class UserDefinedFunctions { [Microsoft.SqlServer.Server.SqlFunction] public static SqlString SqlFunction1() { // Put your code here return new SqlString (string.Empty); } } 

SQL file:

 CREATE PROCEDURE ClrMethod1 RETURNS [int] WITH EXECUTE AS CALLER AS EXTERNAL NAME [MyDB].[UserDefinedFunctions].[SqlFunction1] 

This compiles for me. Note: The namespace was not used again. When I added a new element ... the generated code did not come with a namespace.