April 18, 2010 at 7:59 pm
I am just trying to get my head around CLRs i have one example working however :w00t: there is a catch hehe
I have created a basic class library in visual studio 08 which looks like
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server
Imports System.Runtime.InteropServices
Public Class SVFs
Public Shared Function FormatCurrency(ByVal Number As Double) As SqlTypes.SqlString
Return String.Format("{0:c}", Number)
End Function
End Class
In sql 2005 SSMS I have enabled clr with
sp_configure 'clr enabled', 1
go
Reconfigure
go
Then I have created the assembly
Create assembly SQLLib from 'C:\Documents and Settings\Administrator\My Documents\Visual Studio 2008\Projects\ClassLibrary1\ClassLibrary1\bin\Debug\ClassLibrary1.dll'
with permission_set = safe
However when I go to create a function referencing the assembly it fails
referencing the name space with
Msg 6505, Level 16, State 1, Procedure clrFormatCurrency, Line 1
Could not find Type 'SQLLib.UDFs' in assembly 'ClassLibrary1'.
Create function clrFormatCurrency(@Number float)
RETURNS nVarChar(100)
as
EXTERNAL NAME SQLLib.[SQLLib.SVFs].FormatCurrency
-- i have also tried the namespace SQLLib.[SQLLib.SVFs].FormatCurrency
Thanks for your input or ideas
Msg 6505, Level 16, State 1, Procedure clrFormatCurrency, Line 1
Could not find Type 'SQLLib.UDFs' in assembly 'ClassLibrary1'.
April 18, 2010 at 8:15 pm
🙂 😛 Satisfaction at last it had something to do with rebuilding the solution from visual studio. I thought it looked OK:-P
October 28, 2010 at 10:28 pm
hi,
i'm having the same problem could u tell me how can i fix this problem?
what i did is:
At first initially it was deployed successfully, and i'm able to execute from the sql server. what sql server did by default (i have not created any assembly, any sp)
when i drop the assembly/sp manually here the problem starts, i'm not able to get the assembly when i rebuild/redeploy
in the build menu we have "clean xxx" what it means.
how can i recreate the assembly/sp ?
thanks
🙂
October 5, 2011 at 9:41 am
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 integer
external 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'.
Please let me know what else I should be doing to make this work.
Thank you sooo much.
Dennis
October 2, 2014 at 8:41 am
I know this post is old but I had the same problem and thought I'd share the solution I found.
When you compile the VB assembly in VS, pay attention to the Project Properties. Under the Application tab there is a "Root namespace" textbox. This has to be part of the EXTERNAL NAME syntax when creating the function in T-SQL. I thought that, since I didn't have a namespace in my code, I didn't need to use it in my CREATE FUNCTION code but I kept getting the "Could not find type'xxx' in assembly 'xxx' error until I added it.
-----------------------
INCORRECT:
-----------------------
CREATE FUNCTION dbo.RegExIsMatch
(
@pattern NVARCHAR(4000),
@input NVARCHAR(MAX),
@Options int
)
RETURNS BIT
AS EXTERNAL NAME
RegEx.[RegexFunctions].RegExIsMatch
GO
-----------------------
CORRECT: (Note the syntax must include the "hidden" namespace -- Assembly_Name.[Namespace.Class_Name].Function Name)
-----------------------
CREATE FUNCTION dbo.RegExIsMatch
(
@pattern NVARCHAR(4000),
@input NVARCHAR(MAX),
@Options int
)
RETURNS BIT
AS EXTERNAL NAME
RegEx.[Regex.RegexFunctions].RegExIsMatch
GO
The three biggest mistakes in life...thinking that power = freedom, sex = love, and data = information.
December 3, 2014 at 10:26 pm
IMHO (10/2/2014)
I know this post is old but I had the same problem and thought I'd share the solution I found.When you compile the VB assembly in VS, pay attention to the Project Properties. Under the Application tab there is a "Root namespace" textbox. This has to be part of the EXTERNAL NAME syntax when creating the function in T-SQL. I thought that, since I didn't have a namespace in my code, I didn't need to use it in my CREATE FUNCTION code but I kept getting the "Could not find type'xxx' in assembly 'xxx' error until I added it.
-----------------------
INCORRECT:
-----------------------
CREATE FUNCTION ... AS EXTERNAL NAME RegEx.[RegexFunctions].RegExIsMatch-----------------------
CORRECT: (Note the syntax must include the "hidden" namespace -- Assembly_Name.[Namespace.Class_Name].Function Name)
-----------------------
CREATE FUNCTION ... AS EXTERNAL NAME RegEx.[Regex.RegexFunctions].RegExIsMatch
Hi IMHO. Are you certain of this? Have you tried changing the "Default namespace" value to something like "bob" to see what happens when you rebuild? I cannot (now or in past testing / usage) get the value of that field to matter. The only way I have ever gotten the ClassName portion of the EXTERNAL NAME clause to show up as [name.name] is to have a real namespace defined. Of course, I have only ever done C# so maybe it works differently in VB.Net?
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
December 3, 2014 at 11:16 pm
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
May 5, 2016 at 6:07 pm
IMHO (10/2/2014)
I know this post is old but I had the same problem and thought I'd share the solution I found.When you compile the VB assembly in VS, pay attention to the Project Properties. Under the Application tab there is a "Root namespace" textbox. This has to be part of the EXTERNAL NAME syntax when creating the function in T-SQL. I thought that, since I didn't have a namespace in my code, I didn't need to use it in my CREATE FUNCTION code but I kept getting the "Could not find type'xxx' in assembly 'xxx' error until I added it.
-----------------------
INCORRECT:
-----------------------
CREATE FUNCTION dbo.RegExIsMatch
(
@pattern NVARCHAR(4000),
@input NVARCHAR(MAX),
@Options int
)
RETURNS BIT
AS EXTERNAL NAME
RegEx.[RegexFunctions].RegExIsMatch
GO
-----------------------
CORRECT: (Note the syntax must include the "hidden" namespace -- Assembly_Name.[Namespace.Class_Name].Function Name)
-----------------------
CREATE FUNCTION dbo.RegExIsMatch
(
@pattern NVARCHAR(4000),
@input NVARCHAR(MAX),
@Options int
)
RETURNS BIT
AS EXTERNAL NAME
RegEx.[Regex.RegexFunctions].RegExIsMatch
GO
This saved me! Thanks!
December 12, 2023 at 12:26 pm
Thank you IMHO, your note regarding "hidden" namespace -- 'Assembly_Name.[Namespace.Class_Name].Function Name', helped a lot.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply