Problem Creating Stored Procedure From A Library, DLL

I'm having a problem creating Stored Procedure from a library I have created. I have found that the Create Assembly query causes the Assemby to be placed into the Master Database's Assembly collection. The other is that the Create Procedure Query states that it cannot find the type in the assembly. Can someone see what is going on and let me know

I have also changed the "struct" to a "class" and I get the same error message.

I've included the code snippit, and queries belows:

using System;
using System.Collections.Generic;
using System.Text;

using Microsoft.SqlServer.Server;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace HHLibrary
{
[Serializable]
public struct State
{
[SqlFunction(Name = "GetStateIndexByName",
IsDeterministic = true,
DataAccess = DataAccessKind.Read)]
public static SqlInt32 GetStateIndexByName(SqlString Name, out SqlByte StateIndex)
{
SqlInt32 iResult = 0;
StringBuilder sb = new StringBuilder(500);
SqlCommand command = new SqlCommand();

StateIndex = 255; // Set the output parameter to an illegal value

. . .

return iResult;
}

}
}

************************ Compilation Messages ******************************************

Build started: Project: HeadHunterLibrary, Configuration: Debug Any CPU
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Csc.exe /noconfig /nowarn:1701,1702 /errorreport:prompt /warn:4 /define:DEBUG;TRACE /reference:C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Data.dll /reference:C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.dll /reference:C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Xml.dll /debug+ /debug:full /optimize- /out:obj\Debug\HeadHunterLibrary.dll /target:library State.cs Properties\AssemblyInfo.cs

Compile complete -- 0 errors, 0 warnings
HeadHunterLibrary -> C:\HHLibraries\HeadHunterLibrary.dll
========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========

************************ Trans-SQL Query ******************************************

create assembly HeadHunterLibrary
FROM 'C:\HHLibraries\HeadHunterLibrary.dll'
WITH PERMISSION_SET = SAFE;
GO

CREATE PROCEDURE dbo.GetStateIndexByName
( @.Name nvarchar(25),
@.StateIndex tinyint
)
AS EXTERNAL NAME HeadHunterLibrary.State.GetStateIndexByName;
GO

************************ Messages From Executing The Query ************************************

Msg 6505, Level 16, State 1, Procedure GetStateIndexByName, Line 2
Could not find Type 'State' in assembly 'HeadHunterLibrary'.

You need to specify the database in use. CREATE ASSEMBLY add assemblies to the database in use. For example:

use MyDatabase
create assembly HeadHunterLibrary
FROM 'C:\HHLibraries\HeadHunterLibrary.dll'
WITH PERMISSION_SET = SAFE;
GO

You can double check that it worked by:

SELECT * from myDatabase.sys.assemblies

You need to square bracket the namespace.class. As was written thinks HeadHunterLibrary is a database name. For example:

AS EXTERNAL NAME [HeadHunterLibrary.State].GetStateIndexByName;

Dan

|||

Made the corrections you recommended. Still have one problem that I don’t understand. The query I execute to create the stored procedure is:

USE HeadHunter

CREATE PROCEDURE dbo.GetStateIndexByName

( @.Name nvarchar(25),

@.StateIndex tinyint OUTPUT

)

AS EXTERNAL NAME [HeadHunterLibrary.State].GetStateIndexByName;

When I execute the Create Procedure dbo.GetStateIndexByName I get the following error message.

Msg 102, Level 15, State 1, Procedure GetStateIndexByName, Line 5 Incorrect syntax near ';'.

The managed code looks like this:

namespace HeadHunterLibrary

{

[Serializable]

public class State

{

public static SqlInt32 GetStateIndexByName(SqlString Name, out SqlByte StateIndex)

{

. . .

}

}

}

Can you see what is causing the error?

Thank You

Dan Marks

Logos-Systems@.mchsi.com

|||

Made the corrections you recommended. Still have one problem that I don’t understand. The query I execute to create the stored procedure is:

USE HeadHunter

CREATE PROCEDURE dbo.GetStateIndexByName

( @.Name nvarchar(25),

@.StateIndex tinyint OUTPUT

)

AS EXTERNAL NAME [HeadHunterLibrary.State].GetStateIndexByName;

When I execute the Create Procedure dbo.GetStateIndexByName I get the following error message.

Msg 102, Level 15, State 1, Procedure GetStateIndexByName, Line 5 Incorrect syntax near ';'.

The managed code looks like this:

namespace HeadHunterLibrary

{

[Serializable]

public class State

{

public static SqlInt32 GetStateIndexByName(SqlString Name, out SqlByte StateIndex)

{

. . .

}

}

}

Can you see what is causing the error?

Thank You

Dan Marks

Logos-Systems@.mchsi.com

|||

Made the corrections you recommended. Still have one problem that I don’t understand. The query I execute to create the stored procedure is:

USE HeadHunter

CREATE PROCEDURE dbo.GetStateIndexByName

( @.Name nvarchar(25),

@.StateIndex tinyint OUTPUT

)

AS EXTERNAL NAME [HeadHunterLibrary.State].GetStateIndexByName;

When I execute the Create Procedure dbo.GetStateIndexByName I get the following error message.

Msg 102, Level 15, State 1, Procedure GetStateIndexByName, Line 5 Incorrect syntax near ';'.

The managed code looks like this:

namespace HeadHunterLibrary

{

[Serializable]

public class State

{

public static SqlInt32 GetStateIndexByName(SqlString Name, ref SqlByte StateIndex)

{

. . .

}

}

}

Can you see what is causing the error?

Thank You

Dan Marks

Logos-Systems@.mchsi.com

|||

I see several problems.

The error is probably caused by the lack of assembly name in:

AS EXTERNAL NAME [HeadHunterLibrary.State].GetStateIndexByName;

Try this:

AS EXTERNAL NAME [AssemblyName].[HeadHunterLibrary.State].GetStateIndexByName;

You are also missing a return value (your procedure returns a SqlInt32, but you don't specify that in the stored procedure definition).

Finally, you don't have a StoredProcedure attribute on your method, put this on the line before the method:

[Microsoft.SqlServer.Server.SqlProcedure]

Hope this helps.

|||

Namespace problem?

CREATE PROCEDURE hello

AS

EXTERNAL NAME HelloWorld.StoredProcedures.HelloWorld

GO

EXEC hello

Msg 6505, Level 16, State 1, Procedure hello, Line 1

Could not find Type 'StoredProcedures' in assembly 'HelloWorld'.

Msg 2812, Level 16, State 62, Line 1

Could not find stored procedure 'hello'.

CREATE PROCEDURE hello

AS

EXTERNAL NAME HelloWorld.[HelloWorld.StoredProcedures].HelloWorld

GO

EXEC hello

Hello world! It's now 18/04/2007 14:26:23

try something like:

CREATE PROCEDURE dbo.GetStateIndexByName
( @.Name nvarchar(25),
@.StateIndex tinyint
)
AS EXTERNAL NAME HeadHunterLibrary.[HHLibrary.State].GetStateIndexByName;
GO