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.

Solomon Rutzky · Accepted Answer · 2019-10-15T18:54:09.143

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);

This might be the more elegant solution. My solution has the problem, that the procedure now is twice in the database, once with `dbo` and another one with `foo`. Thanks for the input. — mburm, Oct 15 '19 at 15:15
@mburm Thanks. And I just updated with the dynamic script :-) — Solomon Rutzky, Oct 15 '19 at 15:18
@mburm Please see my most recent update here: you don't need this at all if you are using Visual Studio 2012 or newer. They added a publish profile option for setting the Schema name. — Solomon Rutzky, Oct 15 '19 at 15:58
I already found this option and some Posts which also hint to them. But I don't think that this will help me. I have not only one schema. At the moment there are three ones, probably there will be come some more. If I understand it right with this default schema I can set it to one. — mburm, Oct 15 '19 at 18:48
@mburm Correct, that option is for ALL T-SQL wrapper objects. So you can use that for the Schema that gets the most objects, then use a post-deploy script with the `ALTER SCHEMA` for the other two schemas. I just updated to clarify this particular scenario. — Solomon Rutzky, Oct 15 '19 at 18:53

score 0 · Answer 2 · answered Oct 15 '19 at 14:47

I could it resolve it myself. Just a really stupid mistake…

The name of my assembly has the pattern Company.Area.Technology.ProjectName. If I tried to create a stored procedure with this an error occured that a dot is expected.

CREATE PROCEDURE [foo].[MyProc](@param NVARCHAR(10))
    AS EXTERNAL NAME Company.Area.Technology.ProjectName.ClassName.MyProc
GO

The solution is to set the assembly name between [].

CREATE PROCEDURE [foo].[MyProc](@param NVARCHAR(10))
    AS EXTERNAL NAME [Company.Area.Technology.ProjectName].ClassName.MyProc
GO

This works. So I can wrap the CLR procedure into my own Schema.

Problem: Now the function is included twice in the database, in `dbo` and in `foo`. — mburm, Oct 15 '19 at 15:16

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

2 Answers2