Deploy StoredProcedures written in C#/.NET

FreeCastle

Hi, as the title says it, I've a question about deploying such assemblies to the MSSQL Server (2005). I know that you can deploy it in Visual Studio, but I wondered if there isn't any other way to deploy them WITHOUT Visual Studio ? That would be very neat. Theoretically there must be some possibilities to do this. Maybe someone has experience with it and can give me some hints

Pete OHanlon

You would normally use the command:

CREATE ASSEMBLY MyAssembly FROM 'c:\dev\MyAssembly.dll'
WITH PERMISSION_SET = SAFE

Obviously, this would need to be run using sqlcmd or SQL Server Management Studio.

the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.

FreeCastle

Hi, thank you that was helpful. But maybe I'm to stupid but it doesn't work correct for me. If I execute this e.g. in sqlcmd then I see the deployed assembly, but I don't see the Stored Procedures in my database (as I said I just see the deployed assembly). I tried this with a new clean database, but on my sql server I already have another database with the same assembly (and stored procedures). Could this be the reason that I don't see the Stored Procs in my first mentioned database ? (I think I heard time ago something like, that all Stored Procs are saved globally in the sql server)

Pete OHanlon

That's because the command I showed before is used to make the assembly available to SQL Server. What you need to do is to create the stored procedure that references this. Here is an example of a stored function:

CREATE FUNCTION MyFunction(@Name NVARCHAR(30))
RETURNS INT
AS EXTERNAL NAME [MyAssembly].[MyDll.ClassName].[MyMethod]

I hope that this helps.

the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.

FreeCastle

Mh yes it helps again :-) But I think that I am somehow to stupid... I get always an error saying, that the return types of the t-sql- and clr-types aren't equal. I tried it e.g. this way: create function MyStoredProc(@name nvarchar(30)) RETURNS uniqueidentifier as external name [MyAssembly].[MyClass].[MyStoredProc] In my c#-code I see that the implemented storedprocedure MyStoredProc has an output parameter with type of SqlGuid (which is a uniqueidentifier (I think)). I have also another stored procedure that hasn't any output parameter, but which return value should I specify there ? NULL doesn't work

Pete OHanlon

FreeCastle wrote:

Mh yes it helps again But I think that I am somehow to stupid...

Don't think like that. These are all legitimate questions and they don't reflect on you at all.

FreeCastle wrote:

In my c#-code I see that the implemented storedprocedure MyStoredProc has an output parameter with type of SqlGuid (which is a uniqueidentifier (I think)). I have also another stored procedure that hasn't any output parameter, but which return value should I specify there ? NULL doesn't work

It's hard to say what's going on without seeing your code. If you want to email me it then feel free and I will take a look at it.

the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.

FreeCastle

Hi, did you receive my email ?