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]

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]

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]

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]

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]

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

[Michael Cole]

swetha.m...@gmail.com submitted this idea :

You are replying to a 17 year old message.

--
Michael Cole

[Erland Sommarskog]

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]

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