2

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

user906802
  • 33
  • 6
  • 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 Jun 21 '17 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 Jun 21 '17 at 23:25
  • Are your names match those from sys.assembl* tables? As described here: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql Search on page for `sys.` – shibormot Jun 22 '17 at 02:12
  • Just a guess here, but can you try using `DATETIME2` instead of `DATETIME` for the input param and return value types? – Solomon Rutzky Jun 22 '17 at 03: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 Jun 22 '17 at 14:34

1 Answers1

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

Niels Berglund
  • 1,713
  • 8
  • 6