CLR stored procedure deploys Assembly but not Stored Procedure

Question

I have written a CLR stored procedure (starting with the "Visual C# SQL CLR Database Project" template), and when I deploy it, the assembly is created under MyDatabase > Assemblies, but the corresponding stored procedure isn't being created under MyDatabase > Programmability > Stored Procedures.

Am I missing something? How do I get it to actually create the stored procedure? There isn't much point in having an assembly on the server if I have no way of using it :-(

Interestingly, when I open a very old CLR stored procedure (that still works) and try to deploy it, this is part of the build output:

  Dropping [dbo].[uspGetWhatever]...
  Dropping [MyStoredProcedures]...
  Creating [MyStoredProcedures]...
  Creating [dbo].[uspGetWhatever]...
  The transacted portion of the database update succeeded.
  Deployment completed

Whereas my new CLR stored procedure's output is slightly different, particularly the second "Creating" line with ".[SqlAssemblyProjectRoot]" in square brackets, and not "[dbo].[name of the public void method inside the C# class]" like the old one above:

  Creating [MyStoredProcedures]...
  Adding files to assembly [MyStoredProcedures]
  Creating [MyStoredProcedures].[SqlAssemblyProjectRoot]...
  The transacted portion of the database update succeeded.
  Deployment completed

Something clearly isn't right, but I can't figure it out and have gone nuts comparing/contrasting all other settings between the two projects to make sure they're identical.

Thanks.

Answer 1

Problem solved. I removed all the "static" modifiers on my methods after it complained about it on an earlier attempt (when my project consisted of multiple classes), but now that I have just one class, putting all the "static" modifiers back solved the problem.

Answer 2

This is really a Visual Studio auto-deploy problem, you can always use DDL (CREATE ASSEMBLY and CREATE PROCEDURE) to “manually” deploy an assembly and the associated stored procedure.

 

That being said:

Are you using the same version of Visual Studio each time?

What version of VS are you using?

What version of SQL Server are you trying to deploy to?

Do you have SQL Server Data Tools installed? If so, did you convert the project to the new format?

 

Do both projects have the [SqlProcedure] attribute on the method that constitutes the stored procedure?

 

Cheers, Bob

Answer 3

Glad that you were able to find and fix it.

 

Cheers, Bob

 

"mfearby" wrote in message news:77e0919e-da06-4420-9969-08c077a6c336...

Problem solved. I removed all the "static" modifiers on my methods after it complained about it on an earlier attempt (when my project consisted of multiple classes), but now that I have just one class, putting all the "static" modifiers back solved the problem.

Answer 4

To answer your questions anyway:

Yes, same version of VS
2010
2005
I have no idea :-)
Yes, [SqlProcedure] attribute is there

Manually running CREATE ASSEMBLY, etc, sounds like hard work, so I'm definitely glad I figured out the problem. Cheers.