DennisDA2003 (10/5/2011)
I have the same problem: Following are the details;VB.NET side
Namespace myFunctions
Public Class AFunction
Public Function Factorial(ByRef num As Integer)
If num = 1 Then
Factorial = 1
Else
Factorial = num * Factorial(num - 1)
End If
Return Factorial
End Function
End Class
End Namespace
Assembly name = AFunction, root namespace = AFunction
SQL Server side
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
Apr 22 2011 11:57:00
Copyright (c) Microsoft Corporation
Developer Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2)
Sp_Configure for CLR is turned on
T-SQL:
create function dbo.fnFactorial (@num integer) returns integerexternal name afunction.[myfunctions.afunction].factorial;
error is:
Msg 6505, Level 16, State 2, Procedure fnFactorial, Line 2
Could not find Type 'myfunctions.afunction' in assembly 'AFunction'.
Hi Dennis. Since it has been 3 years and 2 months since this was posted, I suspect this is no longer an issue ;-), but just to have it stated for others that are looking here I can point out what the problem is.
Assuming that the T-SQL code shown above is a copy/paste and not a quick-retype, the issue is merely case-sensitivity. The T-SQL is all lower-case while there are capitals used in the names of: the namespace (i.e. "myFunctions"), the class (i.e. "AFunction"), and the function (i.e. "Factorial"). Those 3 values live inside of the .Net code and are case-sensitive. The name of the assembly lives in SQL Server and the case-sensitivity there depends on the default collation of the database that the assembly is in. Since SQL Server was able to find the assembly, we can assume that the database is set up with a case-insensitive collation. Hence, the following should have worked:
create function dbo.fnFactorial (@num integer) returns integer
external name afunction.[myFunctions.AFunction].Factorial;
P.S. A minor, unrelated bug in the Factorial code: it does not handle 0 properly. An input of 0 should return 1 (http://en.wikipedia.org/wiki/Factorial) but here it looks like it would cause a recursion error as it would do: 0 * -1 * -2 (and so on).
Take care,
Solomon...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR