Reputation: 33
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
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