Create function: Could not find type xxx in assembly yyyy (msg 650

295 views
Skip to first unread message

Frans

unread,
Dec 6, 2009, 8:36:02 AM12/6/09
to
I am working with sqlserver 2008 and visual studio 2008
I want to create an functin:
CREATE FUNCTION fnGetLastStepcompleted
(
@DossierID int
)
RETURNS INT

AS EXTERNAL NAME
GetNotaflowInfoCLR.[GetNotaflowInfoCLR.UserDefinedFunctions].GetLastStepCompleted
GO

error:
Msg 6505 Could not find Type
'My.SQLServer.GetNotaflowInfoCLR.UserDefinedFunctions' in assembly 'UDF_CLR'.

I did try several syntaxes but no luck so far:
--AS EXTERNAL NAME
GetNotaflowInfoCLR.[GetNotaflowInfoCLR.UserDefinedFunctions].GetLastStepCompleted
--AS EXTERNAL NAME
GetNotaflowInfoCLR.[GetNotaflowInfoCLR].[UserDefinedFunctions.GetLastStepCompleted]

Something within 2008?
Thanks for your help,
Frans

Erland Sommarskog

unread,
Dec 6, 2009, 9:33:03 AM12/6/09
to
Frans (Fr...@discussions.microsoft.com) writes:
> I am working with sqlserver 2008 and visual studio 2008
> I want to create an functin:
> CREATE FUNCTION fnGetLastStepcompleted
> (
> @DossierID int
> )
> RETURNS INT
>
> AS EXTERNAL NAME
> GetNotaflowInfoCLR.[GetNotaflowInfoCLR.UserDefinedFunctions].GetLastStepCompleted
> GO
>
> error:
> Msg 6505 Could not find Type
> 'My.SQLServer.GetNotaflowInfoCLR.UserDefinedFunctions' in assembly 'UDF_CLR'.

I would interpret this as that your assembly refers a CLR type which
is not present in SQL Server, or at least not in the database you
are trying to create the assembly in.

I don't know where this My.SQLServer comes from, but I assume that your
assembly has a reference to it.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Frans

unread,
Dec 6, 2009, 11:16:01 AM12/6/09
to
Hi Erland,

the error message (My.SQLserver) is from another try.

This is the create function statement now:


CREATE FUNCTION fnGetLastStepcompleted
(
@DossierID int
)
RETURNS INT

AS EXTERNAL NAME [UDF_CLR].[UserDefinedFunctions].GetLastStepCompleted
GO

Message: Could not find Type 'UserDefinedFunctions' in assembly 'UDF_CLR'.


The Visual Studio 2008 package:

Public Class UserDefinedFunctions
Public Shared Function GetLastStepCompleted(ByVal DossierID As Integer)
As SqlString
Dim svc As New NotaflowClientService.ClientService
Dim D As New NotaflowClientService.Dossier
D = svc.GetDossier(DossierID, True)
Return (D.LastStepCompleted.ToString)
End Function

End Class

I debug and the UDF_CLR Assemblie is created.

It looks like syntax to me, but maybe it's something else.
Help is appreciated.

Erland Sommarskog

unread,
Dec 6, 2009, 12:55:45 PM12/6/09
to
Frans (Fr...@discussions.microsoft.com) writes:
> the error message (My.SQLserver) is from another try.
>
> This is the create function statement now:
> CREATE FUNCTION fnGetLastStepcompleted
> (
> @DossierID int
> )
> RETURNS INT
>
> AS EXTERNAL NAME [UDF_CLR].[UserDefinedFunctions].GetLastStepCompleted
> GO
>
> Message: Could not find Type 'UserDefinedFunctions' in assembly 'UDF_CLR'.
>
>
> The Visual Studio 2008 package:
>
> Public Class UserDefinedFunctions
> Public Shared Function GetLastStepCompleted(ByVal DossierID As
> Integer)
> As SqlString
> Dim svc As New NotaflowClientService.ClientService
> Dim D As New NotaflowClientService.Dossier
> D = svc.GetDossier(DossierID, True)
> Return (D.LastStepCompleted.ToString)
> End Function
>
> End Class

Not sure that I understand the error message, but there is a mismatch
in types between the T-SQL declaration and the VB declaration. Also,
I think you should use SqlInt32 as the type for the parameter, so
that you can handle NULL on input.

If you use Visual Studio, you may have to use extra attributes do
get Visual Studio to do things right. I prefer to compile from the
command line and then use CREATE ASSEMBLY. (Well, it's more than
preference. Visual Studio just goes over my head.)

Michael Coles

unread,
Dec 6, 2009, 4:03:55 PM12/6/09
to
You don't have the .NET namespace in your EXTERNAL NAME clause, like this:

AS EXTERNAL NAME
[UDF_CLR].[<namespace>.UserDefinedFunctions].GetLastStepCompleted

Replace <namespace> with the .NET namespace of your function (it may be a
default namespace, look on the properties tab of the project). Also, as
Erland mentioned, parameters to the function and results returned from the
function should be Sql... nullable types that can handle SQL NULL.

--
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption"
(http://www.apress.com/book/view/1430224649)
----------------

"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9CD9C0A1C...@127.0.0.1...

swetha.m...@gmail.com

unread,
Oct 12, 2016, 10:50:08 AM10/12/16
to
I have the same issue. funtion built from Visualstudio 2008. Did you find a resolution to the issue?

Michael Cole

unread,
Oct 12, 2016, 6:07:28 PM10/12/16
to
swetha.m...@gmail.com submitted this idea :
You are replying to a 17 year old message.

--
Michael Cole

Erland Sommarskog

unread,
Oct 13, 2016, 9:42:54 AM10/13/16
to
Nah, only seven. :-)

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Michael Cole

unread,
Oct 13, 2016, 6:37:42 PM10/13/16
to
Erland Sommarskog pretended :
> Michael Cole (inv...@invalid.com) writes:
>> swetha.m...@gmail.com submitted this idea :
>>> On Sunday, December 6, 2009 at 10:16:01 AM UTC-6, Frans wrote:
>>
>> You are replying to a 17 year old message.
>>
>
> Nah, only seven. :-)

Yep, only seven. But still fairly old. Years tend to blur once you get
past a certain point...

--
Michael Cole
Reply all
Reply to author
Forward
0 new messages