Deploy Visual Studio Database Project Objects to Schema other than "dbo"

Question

I have the following scenario.

I've got a SQLCLR stored procedure in a Visual Studio database project. This should not be in the dbo schema but in foo. As I saw, it's not possible to give a different schema to a SQLCLR stored procedure when using the Visual Studio publish feature.

So I have to wrap it. Now I have two scripts.

One to add the assembly:

CREATE ASSEMBLY [MyAssembly]
FROM 'MyAssembly.dll';
GO

And another to add the stored procedure to the database:

CREATE PROCEDURE [foo].[MyProc](@param NVARCHAR(10))
    AS EXTERNAL NAME MyAssembly.ClassName.MyProc
GO

But now I get an error due to an unresolved reference to MyAssembly. I think this is because it's all (SQLCLR proc, T-SQL wrapper proc, assembly add script) in the same project and it's a reference to itself.

What might be the best way to deploy the SQLCLR stored procedure under my own Schema in the database? If it's possible to add this directly to the SQLCLR implementation would be great.

Thanks in advance.

Answer 1

Actually, this should be a non-issue. Starting in Visual Studio 2012, there's an option to set the Schema for the T-SQL wrapper objects. In the project properties, go to the "Project Settings" tab, and on the right side, under the "General" section, there is a text field labeled: "Default schema". That is the Schema used for the T-SQL wrapper objects (I don't usually use the SSDT deployment so I just reconfirmed this info in Visual Studio 2015 — I know, I know, I really need to update).

This was also mentioned in my answer to: Schema for CLR Stored Procedure During Deployment

If the version of Visual Studio / SSDT you are using does not have the option to set the Schema OR if you need to place the objects into multiple Schemas, then you should be able to simply add a T-SQL post-deployment script that moves the objects to the desired schema. By adding a T-SQL script and setting the properties to "post deployment" (or something similar to that), it will get inserted at the end of the generated publish script.

If it's only a small number of objects that won't really have new objects introduced, you can do explicit statements for each object:

ALTER SCHEMA [foo] TRANSFER [dbo].[MyProc];

If it's a large number of objects and/or new objects will be added occasionally and you don't want to deal with remembering to add them to this post-deployment include script, you can cycle through the list of objects that are associated with that assembly to create a Dynamic SQL script that can move them all without anything but the Assembly name being hard-coded (and technically that might even be able to be dynamic using MSBuild / SSDT variables):

DECLARE @SQL NVARCHAR(MAX) = N'';

SELECT @SQL += N'ALTER SCHEMA [foo] TRANSFER [dbo].'
               + QUOTENAME(obj.[name]) + N';' + NCHAR(0x0D) + NCHAR(0x0A)
FROM   sys.assembly_modules amd
INNER JOIN sys.assemblies asm
        ON asm.[assembly_id] = amd.[assembly_id]
INNER JOIN sys.objects obj
        ON obj.[object_id] = amd.[object_id]
WHERE  asm.[name] = N'Company.Area.Technology.ProjectName' -- do not use [ and ] here
AND    SCHEMA_NAME(obj.[schema_id]) = N'dbo'

PRINT @SQL; -- DEBUG (else, comment out)

EXEC (@SQL);