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

Question

I am working on a Linq based CLR Stored Procedure for some complex filtering and manipulation, which would otherwise require a lot of messy and poorly performant T-SQL code, if implemented in a more "traditional" Stored Procedure.

This is working great, but I can't find how to set the schema of this Stored Procedure in phase of deployment, for a better organization and separation of the database objects in modules.

Any ideas?

Many thanks in advance.

Lange

This request, as far as it relates to setting the Schema in Visual Studio / SSDT deployment, is obsolete as of VS 2012. Please see the UPDATE section at the top of this answer below: https://mcmap.net/q/1077541/-clr-stored-procedures-how-to-set-the-schema-owner – Oahu

Answer 1

When you create the procedure referencing the assembly you can create this wrapper being owned by any schema you want. See This MSDN article on deploying CLR stored procedures for a walkthrough of how to do deploy a stored procedure. By changing the create procedure statement to something like:

CREATE SCHEMA foo

CREATE PROCEDURE foo.hello
AS
EXTERNAL NAME helloworld.HelloWorldProc.HelloWorld

You can now have a procedure owned by the foo schema.

Begotten

Many thanks. I had found that article and I can see it as a workaround, but I was wondering whether there are any other alternative which would not require a wrapper or anyway a second object. Are you aware of any or is this the only actual possibility? Again, thanks for the prompt response :) – Lange

The SQL wrapper is a necessary part. You have to do that anyway - it is an integral part of deploying CLR stored procedures. – Begotten

Many thanks and sorry for the delay, have been busy with something else in the meantime. I am not deploying anymore directly from Visual Studio. Instead, I am deploying via scripts, so I can more easily specify the schema I want by using a wrapper, as you suggested. Many thanks again for your help. – Lange

Answer 2

UPDATE: In Visual Studio 2012 this can now be accomplished via the project properties window of a "SQL Server Database Project." The relevant property is "Default Schema" on the "Project Settings" tab. Modifying this value modifies the generated deployment script to put the Schema name in front of Functions, Stored Procedures, Etc... Be sure to add a Schema object to your project with the same name or you will get build errors.

---

I don't know what version of Visual Studio you are using, but when you create a CLR stored procedure project in Visual Studio 2010, the project includes two SQL scripts: PreDeploymentScript.sql and PostDeploymentScript.sql.

We just use these to maniuplate things the way we want.

In the pre-deployment script, we have something like this:

-- DROP EXISTING ITEM FROM CURRENT SCHEMA
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Utilities].[fn_Create_Md5_Hash]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [Utilities].[fn_Create_Md5_Hash]
GO

Then, in the post-deployment script, we have this:

-- DEPLOYMENT WIZARD RECREATES ITEM IN dbo SCHEMA
-- DROP NEW ITEM FROM dbo SCHEMA
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Create_Md5_Hash]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_Create_Md5_Hash]
GO

-- RECREATE THE ITEM BACK IN THE SCHEMA YOU WANT
CREATE FUNCTION [Utilities].[fn_Create_Md5_Hash](@source [varbinary](max))
RETURNS [varbinary](8000) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [NameSpace].[UserDefinedFunctions].[fn_Create_Md5_Hash]
GO

Hope that helps!

Esperanto