CODEWITHSUNDEEP
sql-serversqlclrsql-server-data-tools
bopapa_1979
bopapa_1979

Reputation: 9237

Schema for CLR Stored Procedure During Deployment

I'm interested in configuring Visual Studio (2010) so that when deploying C# CLR Database projects, it puts stuff in schemas other than DBO. I understand I can update the function/procedure/etc... wrappers it creates manually to make this happen: CLR Stored Procedures: how to set the schema/owner?

However, I'd really like to automate the process somehow. If anybody knows, I'd really appreciate the answer!

Upvotes: 5

Views: 2931

Answers (5)

Mark Sowul
Mark Sowul

Reputation: 10610

In the SQLCLR properties for the SSDT project, you can turn off "Generate DDL". Then you can create your own SQL object that attaches to 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

This will allow you to put different objects in different schemas

Upvotes: 1

Solomon Rutzky
Solomon Rutzky

Reputation: 48874

It should be mentioned that this question is obsolete as of Visual Studio 2012 which has a "Default Schema" configuration field for the generated T-SQL wrapper objects. This has also been noted in the "Update" section at the top of the following answer ;-) :

CLR Stored Procedures: how to set the schema/owner?

Upvotes: 1

TechDo
TechDo

Reputation: 18659

You can change stored procedure from one schema to another using

ALTER SCHEMA Org TRANSFER dbo.spUdpateCompany

Upvotes: 4

Nilesh Hirapra
Nilesh Hirapra

Reputation: 51

You may put scripts for each object in post deployment script like below. Below script re-creates stored procedure with schema [Org]. Hope this helps.

Step1 - Remove Stored procedure added automatically by project since it is created with default schema [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

Step2 - Remove stored procedure if already exist in [Org] schema and re-create stored procedure in [Org] schema.

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

Upvotes: 1

peter
peter

Reputation: 11

It seems to be security related and done "by design". http://support.microsoft.com/kb/918346

Upvotes: 1

Related Questions