Problem creating a function from an assembly

I have compiled a dll with the following code:

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes

Public Class SICTrans

Public Shared Function TransSIC(ByVal inpSIC As String) As String
'Dim conn As SqlConnection = New SqlConnection("context connection=true")
Dim NewSIC, TempSIC, tempFSIC As String

If Len(NZ(inpSIC)) > 0 Then
TempSIC = NZ(inpSIC)

If Len(TempSIC) < 5 Then TempSIC = Left("00000", 5 - Len(TempSIC))

tempFSIC = Left(TempSIC, 2) + "." + Mid(TempSIC, 3, 2)

If Val(Right(TempSIC, 1)) > 0 Then

tempFSIC = tempFSIC + "/" + Right(TempSIC, 1)
End If
End If

NewSIC = tempFSIC

TransSIC = NewSIC

End Function
Public Shared Function NZ(ByVal input As String) As String
If Not (input Is Nothing) Then
Return input
Exit Function
End If
Return String.Empty

End Function
End Class

Which compiles fine...

i then use the following code to create the assembly in SQL which is fine:

USE NARD
GO
CREATE ASSEMBLY SICCodeTrans
FROM 'c:\SICCodeTrans.dll'
WITH PERMISSION_SET = SAFE
GO

but when i goto create the function with the following code it wont have it!

CREATE FUNCTION TransSICCode(@.inpSIC varchar)
RETURNS varchar
AS EXTERNAL NAME
SICCodeTrans.SICTrans.TransSIC
GO

It gives me the following error message

Msg 6505, Level 16, State 1, Procedure TransSICCode, Line 1
Could not find Type 'SICTrans' in assembly 'SICCodeTrans'.

Any ideas?

Thanks

Marek Kluczynski

I think what's going on here is that there is a root namespace on the project.

IIRC, in a C# project VS will simply insert the root namespace delcaration into your code when you use certain templates. In VB, on the other hand, the compiler decides to insert the namespace definition without having it show up in the code.

When you go to create the function in SQL, it can't find it because there is no SICTrans class in the default namespace, there is a SICTrans class in whatever root namespace was set on the project.

You can right-click on the project in VS and pull up the propertiesunder the "Application" tab you'll see this.

Assuming your namespace is NAMESPACE, you'd think you could do the following:

CREATE FUNCTION TransSICCode(@.inpSIC varchar)
RETURNS varchar
AS EXTERNAL NAME
SICCodeTrans.NAMESPACE.SICTrans.TransSIC
GO

This won't workit's a (rather annoying) syntax error. You have to write it like this:

CREATE FUNCTION TransSICCode(@.inpSIC varchar)
RETURNS varchar
AS EXTERNAL NAME
SICCodeTrans.[NAMESPACE.SICTrans].TransSIC
GO

Hope this works for you. Let us know. :)

Cheers
-Isaac

|||

yes this seems to have resolved the namespace error.

I now have the following error:

Msg 6551, Level 16, State 2, Procedure TransSICCode, Line 1

CREATE FUNCTION for "TransSICCode" failed because T-SQL and CLR types for return value do not match.

Many thanks

Marek

|||I suspect two things. First, you should map the string to an nvarchar, not a varcharCLR strings are Unicode. Second, you need to give a parameter to the nvarchar, e.g., nvarchar(4000), nvarchar(max), etc.

Cheers,
-Isaac|||Many thanks for all your help