将 Visual Studio 数据库项目对象部署到架构而不是"dbo"

Question

我有以下情况。

我在Visual Studio数据库项目中有一个SQLCLR存储过程。这不应该在dbo模式中，而是在foo中。正如我所看到的，在使用Visual Studio发布功能时，不可能为SQLCLR存储过程提供不同的架构。

所以我必须把它包起来。现在我有两个脚本。

一个用于添加程序集：

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

另一个将存储过程添加到数据库：

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

但是现在由于未解析对MyAssembly的引用，我收到错误。我认为这是因为它都是(SQLCLR proc、T-SQL 包装器进程、程序集添加脚本(在同一个项目中，并且它是对自身的引用。

在数据库中我自己的架构下部署 SQLCLR 存储过程的最佳方法是什么？如果可以将其直接添加到 SQLCLR 实现中，那就太好了。

提前谢谢。

Answer 1

实际上，这应该不是问题。从 Visual Studio 2012 开始，有一个选项可以设置 T-SQL 包装器对象的架构。在项目属性中，转到"项目设置"选项卡，在右侧的"常规"部分下，有一个标有："默认架构"的文本字段。这是用于 T-SQL 包装器的架构(我通常不使用 SSDT 部署，所以我只是在 Visual Studio 2015 中重新确认了此信息 - 我知道，我知道，我真的需要更新(。

我在以下回答中也提到了这一点：部署期间 CLR 存储过程的架构

如果您使用的 Visual Studio/SSDT 版本没有设置架构的选项，或者如果您需要将对象放入多个架构中，那么您应该能够简单地添加一个 T-SQL 后期部署脚本，将对象移动到所需的架构。通过添加 T-SQL 脚本并将属性设置为"部署后"(或类似内容(，它将插入到生成的发布脚本的末尾。

如果只有少数对象不会真正引入新对象，则可以为每个对象执行显式语句：

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

如果是大量对象和/或偶尔会添加新对象，并且您不想记住将它们添加到此部署后包含脚本中，则可以循环浏览与该程序集关联的对象列表以创建一个动态 SQL 脚本，该脚本可以移动它们，而无需硬编码程序集名称(从技术上讲，甚至可以使用MSBuild/SSDT 变量(：

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

Answer 2

我可以自己解决。只是一个非常愚蠢的错误...

我的程序集的名称具有模式Company.Area.Technology.ProjectName。如果我尝试使用它创建一个存储过程，则会发生一个错误，即需要点。

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

解决方案是在[]之间设置程序集名称。

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

这行得通。因此，我可以将 CLR 过程包装到我自己的架构中。