CODEWITHSUNDEEP
sql-serverdll.net-assemblysqlclr
user906802
user906802

Reputation: 33

T-SQL cannot find CLR function in assembly - no namespace?

We are porting a SQL Server database that includes a CLR assembly with date functions (the developer is long-gone). I created the assembly from the DLL (it is listed in sys.assemblies):

CREATE ASSEMBLY ArkaDB FROM 'C:\Temp\ArkaDB.dll' WITH PERMISSION_SET = SAFE;

But when I try to create a SQL stored procedure for the GetOIDate function:

create FUNCTION [dbo].[GetOIDate](@ActivityDate [datetime])
RETURNS [datetime] WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [ArkaDB].[ArkaDB.UserDefinedFunctions].[GetOIDate]

it gives the error "Msg 6505, Level 16, State 2, Procedure GetOIDate, Line 2 Could not find Type 'ArkaDB.UserDefinedFunctions' in assembly 'ArkaDB'."

You can see the DLL structure along with the function code in the ILSpy screenshot below. Note the dash "-" for the namespace. From this question, we are supposed to include a namespace in the EXTERNAL NAME specification. But what if there is no namespace? The answer here indicates you simply provide the class like "EXTERNAL NAME [SqlClr].Math.[Add]". I have tried every variation that I can think of and they all give the same could-not-find error:

EXTERNAL NAME [ArkaDB].[ArkaDB.UserDefinedFunctions].[GetOIDate]
EXTERNAL NAME [ArkaDB].UserDefinedFunctions.[GetOIDate]
EXTERNAL NAME [ArkaDB].[.UserDefinedFunctions].[GetOIDate]
EXTERNAL NAME [ArkaDB].[-.UserDefinedFunctions].[GetOIDate]
EXTERNAL NAME [ArkaDB].[''.UserDefinedFunctions].[GetOIDate]
EXTERNAL NAME [ArkaDB].[ .UserDefinedFunctions].[GetOIDate]

Any ideas? We are running SQL Server 2012, while the DLL was originally developed for and installed in 2008 R2. ILSpy for ArkaDB DLL

Edit: for srutzky here is public class definition in ILSpy UserDefinedFunctions class

Upvotes: 2

Views: 1323

Answers (1)

Niels Berglund
Niels Berglund

Reputation: 1698

Unfortunately I cannot comment (not reputation enough), so let;s try with an answer instead:

You wrote: "If I script the SQL function create in the old database, it generates "EXTERNAL NAME [ArkaDB].[UserDefinedFunctions].[GetOIDate]".". What if you script the assembly from the old database (where it works, I assume), and deploy to the new database that way.

Something like:

CREATE ASSEMBLY ArkaDB FROM binary_representation WITH PERMISSION_SET = SAFE;

That way you should at least be able to get it deployed. If no namespace exists, in your assembly, the create function should be like srutzky says:

CREATE FUNCTION [dbo].[GetOIDate](@ActivityDate [datetime])
RETURNS [datetime] WITH EXECUTE AS CALLER
EXTERNAL NAME [ArkaDB].[UserDefinedFunctions].[GetOIDate]

Let us know please how it goes.

Niels

Upvotes: 1

Related Questions