Help Troubleshoot "Could not find Type in assembly"

I am attempting to create a CLR Procedure. I was able to create the assembly, but I am unable to create a procedure on the assembly. This is the error I receive:

Msg 6505, Level 16, State 1, Procedure DINEServiceProc, Line 2

Could not find Type 'DINEServiceProc' in assembly 'DINEService'

Here is the VB code to create the class:

<code>

Imports System

Imports System.Data

Imports System.Data.Sql

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Partial Public Class DINEServiceProc

<Microsoft.SqlServer.Server.SqlProcedure()> _

Public Shared Sub ServiceProc(ByVal iMsg As String, ByVal iMsgType As String)

Dim conn As SqlConnection

'Create an in-process connection to the instance of SQL Server

conn = New SqlConnection("Context Connection=True")

Dim DINEService As New DINEService

Try

conn.Open()

DINEService.ProcessStartRequest(iMsg, iMsgType)

Catch sqe As SqlException

'Console.WriteLine(sqe.Message)

Return

Finally

conn.Close()

End Try

End Sub

End Class

</code>

And here is the code to create the assembly and the procedure:

<code>

USE [ServiceBrokerTest]

GO

/****** Object: SqlAssembly [DINEService] Script Date: 01/03/2006 10:38:00 ******/

CREATE ASSEMBLY [DINEServiceProc]

AUTHORIZATION [dbo]

FROM 'D:\EHIT\ServiceBroker\DINEService\DINEService\bin\Debug\DINEService.dll'

WITH PERMISSION_SET = SAFE

GO

CREATE PROCEDURE dbo.DINEServiceProc

(

@.msg nvarchar(MAX),

@.msgType nvarchar(MAX)

)

AS EXTERNAL NAME DINEServiceProc.DINEServiceProc.ServiceProc;

</code>

What am I doing wrong here?

As this is a vb assembly I'd say you're falling foul of VB creating a default namespace around your classes. Have a look in ildasm or reflector at your assembly and see if you don't have a namespace in there. In which case the EXTERNAL NAME in your CREATE PROCEDURE statement should look like so: AS EXTERNAL NAME DINEServiceProc.[NamespaceName.DINEServiceProc].ServiceProc.

Niels
|||

As nielsb said, you're running into the VB default namespace issue.

CREATE PROCEDURE ....

AS EXTERNAL NAME [DINEServiceProc].[DINEServiceProc.DINEServiceProc].[ServiceProc]

should work for you.

|||Yes, that was it. Thanks to both of you.|||

Hi

I just wanted to thank you folks for this one (i.e. The NAMESPACE ).

I have been battling with this morning. The problem that I found was.. that in the example that I was following, the author has manually compiled the .dll in a directory with a similar name to that of the namespace and warns you in the creation of the assembly to use the fully qualified path to the directory . This is true and worked out fine.

Where I came unstuck was in the creation of the Stored Procedure by passing the same fully quailified path (minus the c:\ ) INSTEAD of the namespace.

BTW I was using C#

regards Steve

|||I had the same issue, and this was the fix. Thanks for postings!!!|||I had similar problems. I found I was using the wrong case for the namespace. I found the "MSIL Disassembler" helped me solve my problems.|||

I just had the same problem. It seems C# also does the same thing with the 'hidden' namespace. The documentation and error message should be updated to include this condition.

-Lukasz

|||Thanks, for your reply!

I also use C# and the problem occurred for me as well, you've saved me a lot of time.

Regards,
Peter Larsson!