使用文件路径在UNSAFE模式下创建汇编不会在数据库中创建函数

I am trying to register CLR assembly in SQL Server with following statement:

我试图在SQL Server中使用以下语句注册CLR程序集:

CREATE ASSEMBLY SQLCLRTest
AUTHORIZATION [dbo]
FROM 'C:\MyApp\SQLCLRTest.dll'
WITH PERMISSION_SET = UNSAFE;

The CLR assembly is signed with a pfx key, and I have already created an Asymmetric key and a SQL login like to allow installing this particular assembly with UNSAFE permissions set.

CLR程序集使用pfx密钥签名,我已经创建了一个非对称密钥和SQL登录,以允许安装此特定程序集并设置了UNSAFE权限。

The problem is that above statement creates the assembly in database, but it doesn't show up any sql server functions I have created in the assembly.

问题是上面的语句在数据库中创建程序集,但它没有显示我在程序集中创建的任何sql server函数。

I dont see any issue with my CLR assembly, because when I publish the database through SQL Server Database project in Visual Studio I can see all functions getting created in the database. When I looked into the Publish scripts, I could see the database project uses Binary of the assembly. That means, it executes something like

我没有看到我的CLR程序集有任何问题,因为当我通过Visual Studio中的SQL Server数据库项目发布数据库时,我可以看到在数据库中创建的所有函数。当我查看发布脚本时,我可以看到数据库项目使用程序集的二进制。这意味着,它执行类似的东西

CREATE ASSEMBLY SQLCLRTest
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF000.......
WITH PERMISSION_SET = UNSAFE;

So my questions is why the assembly gets registered properly through the DLL binaries, but not when I use DLL's path?

所以我的问题是为什么程序集通过DLL二进制文件正确注册,但不是在我使用DLL的路径时?

Any help on this will be much appreciated.

任何有关这方面的帮助将非常感激。

Database server: SQL Server 2012. Database project is created in Visual Studio 2013. CLR assembly is created as a separate class library project in Visual Studio 2013.

数据库服务器:SQL Server 2012.数据库项目在Visual Studio 2013中创建.CLR程序集在Visual Studio 2013中创建为单独的类库项目。

2 个解决方案

#1

why the assembly gets registered properly through the DLL binaries, but not when I use DLL's path?

为什么程序集通过DLL二进制文件正确注册,但不是在我使用DLL的路径时?

The issue is not the creation (what you are referring to as getting registered) of the assembly. If you do not get an error when running CREATE ASSEMBLY then it did get created successfully.

问题不在于装配的创建(你所指的是注册)。如果在运行CREATE ASSEMBLY时没有收到错误,那么它确实已成功创建。

But, the assembly itself merely contains the .NET methods that can be referenced by T-SQL wrapper objects, but creating an assembly does not automagically create those T-SQL wrapper objects. Those need to be explicitly created.

但是,程序集本身只包含可由T-SQL包装器对象引用的.NET方法,但创建程序集不会自动创建这些T-SQL包装器对象。那些需要明确创建。

The main publish script used by SSDT is a diff / incremental script to bring the target DB up to the state implied by your project. If you want to always get the CREATE statements regardless of any changes being deployed, be sure to:

SSDT使用的主要发布脚本是diff / incremental脚本,用于将目标数据库升级到项目隐含的状态。如果您想要始终获取CREATE语句而不管部署的任何更改,请确保:

Definitely check the "Generate DDL" option on the "SQLCLR" tab of Project Properties. This option controls whether or not the wrapper object CREATE statements (i.e. the CREATE PROCEDURE, CREATE FUNCTION, CREATE TYPE, CREATE AGGREGATE, and CREATE TRIGGER statements) are generated.

绝对检查项目属性的“SQLCLR”选项卡上的“生成DDL”选项。此选项控制是否生成包装器对象CREATE语句(即CREATE PROCEDURE,CREATE FUNCTION,CREATE TYPE,CREATE AGGREGATE和CREATE TRIGGER语句)。
Optionally check the "Create script (.sql file)" check-box on the "Project Settings" tab of Project Properties. With this checked, each time you do a build there will be a {Build tab.BuildOutputPath}\{Build tab.BuildOutputFileName}_Create.sql script. That _Create.sql script will always contain the CREATE ASSEMBLY statement.

(可选)选中“项目属性”的“项目设置”选项卡上的“创建脚本(.sql文件)”复选框。选中此选项后,每次进行构建时都会有一个{Build tab.BuildOutputPath} \ {Build tab.BuildOutputFileName} _Create.sql脚本。 _Create.sql脚本将始终包含CREATE ASSEMBLY语句。

When you do a build, there will always be a obj\Debug\{SQLCLR tab.AssemblyName}.generated.sql script. That script will always contain the CREATE ASSEMBLY statement. That script will also contain the CREATE PROCEDURE, CREATE FUNCTION, CREATE TYPE, CREATE AGGREGATE, and CREATE TRIGGER statements IF you have the "Generate DDL" option checked (#1 above). This script is not exactly runnable as it is out of order, but the statements in this script are used to generate the Publish and optional Create scripts.

当你进行构建时,总会有一个obj \ Debug \ {SQLCLR tab.AssemblyName} .generated.sql脚本。该脚本将始终包含CREATE ASSEMBLY语句。如果选中“生成DDL”选项(上面的#1),该脚本还将包含CREATE PROCEDURE,CREATE FUNCTION,CREATE TYPE,CREATE AGGREGATE和CREATE TRIGGER语句。此脚本不是完全可运行的,因为它不正常,但此脚本中的语句用于生成发布和可选的创建脚本。

If you have the "Create script" option checked (#2 above), then, assuming that you also have the "Generate DDL" option checked (#1 again), the CREATE PROCEDURE, CREATE FUNCTION, CREATE TYPE, CREATE AGGREGATE, and CREATE TRIGGER statements will also be in a {Build tab.BuildOutputPath}\{Build tab.BuildOutputFileName}_Create.sql script. In this script, the statements will be in the correct order.

如果选中了“创建脚本”选项(上面的#2),那么,假设您还选中了“生成DDL”选项(再次#1),CREATE PROCEDURE,CREATE FUNCTION,CREATE TYPE,CREATE AGGREGATE和CREATE TRIGGER语句也将位于{Build tab.BuildOutputPath} \ {Build tab.BuildOutputFileName} _Create.sql脚本中。在此脚本中,语句的顺序正确。

#2

The publish step from Visual Studio also includes a separate script to create the functions and procedures embedded in your SQLCLR assembly. As a result, Visual Studio is doing both of these jobs for you.

Visual Studio的发布步骤还包括一个单独的脚本,用于创建SQLCLR程序集中嵌入的函数和过程。因此,Visual Studio正在为您完成这两项工作。

Execution of the CREATE ASSEMBLY script does not create the functions or procs. It merely exposes the assembly to the SQLCLR. You must create them manually/separately when creating the assembly in the manner.

执行CREATE ASSEMBLY脚本不会创建函数或过程。它只是将组件暴露给SQLCLR。在以这种方式创建装配时,必须手动/单独创建它们。

#1