5

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!

Community
  • 1
  • 1
bopapa_1979
  • 8,949
  • 10
  • 51
  • 76

5 Answers5

4

You can change stored procedure from one schema to another using

ALTER SCHEMA Org TRANSFER dbo.spUdpateCompany
TechDo
  • 18,398
  • 3
  • 51
  • 64
  • Now THIS is helpful. It isn't based in the IDE, but it sure cuts down the amount of T-SQL required in the post-deployment script! No more dropping and re-creating. – bopapa_1979 Jan 31 '13 at 18:18
  • It may not be IDE based, but there is a Connect item you can vote on to add it as an Attribute (which I would like) https://connect.microsoft.com/SQLServer/feedback/details/253605/sqlfunction-should-have-a-schema-parameter#details. For now: Add the "Alter Schema" line to the PostDeployScript.sql under your project and you're good to go, deploy away. Why no upvote @Eric? In my opinion, this is the de-facto answer. – MikeTeeVee Mar 05 '13 at 23:02
  • @MikeTeeVee just forgot somehow. This is certainly a more concise script. I'll upvote, but will have to test before I change the accepted answer. Also, I think I found a way to do it in the IDE ("Assembly Owner" on the Database tab of the project properties), which would mean I'd accept my own answer once I provide it... again, have to test first. – bopapa_1979 Mar 05 '13 at 23:42
  • The problem with this solution is that it doesn't support cases where the object being moved is referenced by other objects. The SSDT model thinks the object is in dbo and therefore your code must reference that object. However, it won't be there anymore after you move it. Mark Sowul's answer below is the best solution. – Jason Pease Jul 11 '19 at 18:03
1

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?

Community
  • 1
  • 1
Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
1

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

peter
  • 11
  • 1
  • 2
    I'm talking about what happens when you right-click a SQL CLR project in Visual Studio and click "Deploy." It puts your assembly in SQL Server, and then makes T-SQL wrapper functions and stored procedures that call your assembly methods. These wrappers always go into the dbo schema, so far as I can tell. I would like to put them in a separate schema for organizational purposes. So far, the best I have managed is to manually edit the pre and post deployment SQL scripts in the project to drop and re-create the items in the Schema I want them in, but it is a manual process for each item. – bopapa_1979 Jan 04 '11 at 18:45
1

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

Mark Sowul
  • 10,244
  • 1
  • 45
  • 51
1

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
Nilesh Hirapra
  • 41
  • 2
  • This whole thing can be simplified to one line: ALTER SCHEMA Org TRANSFER dbo.spUdpateCompany. Any objects associated with the DLL are necessarily dropped before deployment because they are dependent on the DLL. – Griffin Mar 14 '12 at 00:36
  • I was originally hoping there would be a place to configure the Schema in Visuatl Studio, but I never did find one. This is the approach we took... Pre and Post-Deployment scripts. – bopapa_1979 Jun 27 '12 at 18:42
  • @Griffin `ALTER SCHEMA Org TRANSFER dbo.spUdpateCompany` is great code... This has to be the best answer I guess :) – TechDo Jan 25 '13 at 09:41
  • When I run my application, I get an deployment error `Cannot drop the procedure, because it does not exist or you do not have permission. DROP ASSEMBLY failed because is referenced by object.`. Is there any option to eliminate this error? – TechDo Jan 25 '13 at 09:47