CLR Could not find the type in the assembly

  • tchaplin 75200

    Old Hand

    Points: 344

    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'.

  • tchaplin 75200

    Old Hand

    Points: 344

    🙂 😛 Satisfaction at last it had something to do with rebuilding the solution from visual studio. I thought it looked OK:-P

  • SQL*

    SSCertifiable

    Points: 6638

    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

    🙂

  • DennisDA2003

    SSC Journeyman

    Points: 95

    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

  • IMHO

    Ten Centuries

    Points: 1006

    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

  • Solomon Rutzky

    SSCoach

    Points: 16129

    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 Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • Solomon Rutzky

    SSCoach

    Points: 16129

    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 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'.

    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 Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • dagray

    SSC Enthusiast

    Points: 107

    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!

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply