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

Question

I have been trying to find the answer to this simple question - but so far couldn't figure it out.

Let say I have MyDb.sqlproj, with various sql content (sprocs, views, trigger, etc).

I have added a new UDF through Add-> New item -> SQL CLR C#, User Defined Function.

For example:

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 MyDb.sqlproj Properties, SQLCLR tab MyDb is the name of assembly and default namespace

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

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

I seem to tried everything to get the last line to compile. It cannot resolve the reference and get:

SQL71501: Function: [name of my sql function] has an unresolved reference to Assembly [MyDb]

Please point me to the right way to get it working. What could I be missing?

I am using VS2010 SP1 and latest version of SSDT

I updated my post after realizing your *.cs file was actually inside your SSDT project. Hopefully that helps you out. — AaronLS
– AaronLS, Commented May 21, 2012 at 20:43
This is actually kinda awesome cause I didn't realize SSDT projects could compile CS until I just now tried it. I was about to get all up on my high horse and be like "The SSDT project doesn't compile CS files! You can even look at the build output and see"... oh wait it is actually generating a DLL, I open it up in .net reflector, and sure enough there's the new CLR function :D I'm not sure how project renames might effect the assembly name. .NET Reflector can be used to check the assembly name of the generated DLL later if need be(under the Project/Obj/Debug/ folder) — AaronLS
– AaronLS, Commented May 21, 2012 at 20:49
Nevermind, that is found under Project Properties->SQLCLR->Assembly Name — AaronLS
– AaronLS, Commented May 21, 2012 at 20:50
Yeah, that's a neat feature of sqlproj! SQLCLR is a part of database project now - which is in fact how it is. — Paul
– Paul, Commented May 21, 2012 at 22:16

AaronLS · Accepted Answer · 2012-05-21 20:42:10Z

You must add your compiled DLL containing your CLR code as a Reference. So under your MyDb SSDT project->References(right click)->Add Reference, browse to the DLL.

You could probably get away with referencing the project instead of the DLL if you had the CLR(class library) project in the same solution, but in my case I am referencing a DLL(compiled for a seperate solution).

As far as the format of the AS EXTERNAL NAME line:

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

Note: For CLR objects I remove the namespaces from around my classes/code just to simplify things, because this step is usually the most problematic. It is easy to confuse the AssmeblyName/DLL Name/Namespace. The AssemblyName is found in your CLR class library project by accessing the Project Properties->Application->"Assembly Name:". I would remove any non alpha-numeric/spaces from it just to simplify the name and rule out that as a problem.

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

Ok reallized that you have a *.cs file actually inside the same SSDT project. so in that 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: Again no namespace was used. When I did Add New Item... the generated code did not come with a namespace.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related