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

[Frans]

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]

Post by Frans
I am working with sqlserver 2008 and visual studio 2008
CREATE FUNCTION fnGetLastStepcompleted
(
@DossierID int
)
RETURNS INT
AS EXTERNAL NAME
GetNotaflowInfoCLR.[GetNotaflowInfoCLR.UserDefinedFunctions].GetLastStepCompleted
GO
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.

[Frans]

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]

Post by Frans
the error message (My.SQLserver) is from another try.
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'.
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]

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)
----------------

Post by Erland Sommarskog

Post by Frans
the error message (My.SQLserver) is from another try.
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'.
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.)
--
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[s***@gmail.com]

Post by Frans
Hi Erland,
the error message (My.SQLserver) is from another try.
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'.
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.

I have the same issue. funtion built from Visualstudio 2008. Did you find a resolution to the issue?

[Michael Cole]

Post by s***@gmail.com

Post by Frans
Hi Erland,
the error message (My.SQLserver) is from another try.
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'.
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.

I have the same issue. funtion built from Visualstudio 2008. Did you find a
resolution to the issue?

You are replying to a 17 year old message.

[Erland Sommarskog]

Post by Michael Cole
You are replying to a 17 year old message.

Nah, only seven. :-)

[Michael Cole]

Post by Erland Sommarskog

Post by Michael Cole
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...