Schema for CLR stored procedure during deployment

Question

I am interested in setting up Visual Studio (2010) so that when deploying C # CLR database projects, it puts stuff in schemas other than DBO. I understand that I can update the wrappers of the function / procedure / etc ... that it creates manually for this to happen: Stored CLR procedures: how to set the schema / owner?

However, I would really like to automate the process. If anyone knows, I would really appreciate an answer!

Answer 1

You can change the stored procedure from one schema to another using

ALTER SCHEMA Org TRANSFER dbo.spUdpateCompany 

Answer 2

Security seems to be connected and executed "by design." http://support.microsoft.com/kb/918346

Answer 3

You can put scripts for each object in a post-deployment script, as shown below. Below the script, the stored procedure with the [Org.] Schema is recreated. Hope this helps.

Step1 - Delete the saved procedure automatically added by the project, since it is created according to the default scheme [dbo].

 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spUpdateCompany]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[spUpdateCompany] GO 

Step 2 - Delete the stored procedure, if it already exists in the [Org.] Scheme, and recreate the stored procedure in the [Org.] Scheme.

 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Org].[spUpdateCompany]') AND type in (N'P', N'PC')) DROP PROCEDURE [Org].[spUpdateCompany] GO CREATE PROCEDURE [Org].[spUpdateCompany] @Id int, @Name [nvarchar](4000) WITH EXECUTE AS CALLER AS EXTERNAL NAME [SQLServerProject.CLR].[StoredProcedures].[spUpdateCompany] GO 

Answer 4

It should be noted that this question is outdated, as for Visual Studio 2012, which has a default schema configuration field for the created T-SQL shell objects. This was also noted in the “Update” section at the top of the next answer ; -) :

CLR Stored Procedures: How to Set Schema / Owner?

Answer 5

In the SQLCLR properties for the SSDT project, you can disable "Create DDL". You can then create your own SQL object that joins the CLR assembly. For example, add a new stored procedure as

CREATE PROCEDURE [schema].[StoredProcedure] @parameter BIGINT NULL AS EXTERNAL NAME [AssemblyNameFromProjectProperties].[ClassWithProcedure].[Method]; GO