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

Question

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

This one -- EXTERNAL NAME [ArkaDB].UserDefinedFunctions.[GetOIDate] -- should be correct, or you can add square brackets around the class name: EXTERNAL NAME [ArkaDB].[UserDefinedFunctions].[GetOIDate]. Is that Class marked as "public"? — Solomon Rutzky
– Solomon Rutzky, Commented Jun 21, 2017 at 16:33
Thanks for the reply. Yes the Class is public -- see image added to post above. You are right about the should-be-correct format. If I script the SQL function create in the old database, it generates "EXTERNAL NAME [ArkaDB].[UserDefinedFunctions].[GetOIDate]". However this gives similar error when run in new server "Could not find Type 'UserDefinedFunctions' in assembly 'ArkaDB'". My next step is try to rebuild the DLL in Visual Studio but that is always fraught for legacy code: Do I have the right source? Are there VS version issues? — user906802
– user906802, Commented Jun 21, 2017 at 23:25
Are your names match those from sys.assembl* tables? As described here: learn.microsoft.com/en-us/sql/t-sql/statements/… Search on page for sys. — shibormot
– shibormot, Commented Jun 22, 2017 at 2:12
Just a guess here, but can you try using DATETIME2 instead of DATETIME for the input param and return value types? — Solomon Rutzky
– Solomon Rutzky, Commented Jun 22, 2017 at 3:57
Are you 100% sure that the DLL you loaded into SQL Server 2012 is the exact same one that was used to load 2008 R2? You should compare the values found in the content column using the following query: SELECT DATALENGTH([content]) AS [bytes], * FROM sys.assembly_files WHERE [name] LIKE N'%ArkaDB%' AND [file_id] = 1;. If they have the same number of bytes then you need to compare the content. You can copy and paste the content values from both servers into a query where you create them as either VARBINARY(MAX) or VARCHAR(MAX) variables and see if they are equal. — Solomon Rutzky
– Solomon Rutzky, Commented Jun 22, 2017 at 14:34

Niels Berglund · Accepted Answer · 2017-07-02 08:29:48Z

1

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

answered Jul 2, 2017 at 8:29

Niels Berglund

1,70810 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

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