SQL Server实现CLR步骤及其需要配置注意的问题（转）

介绍 
我们一起来做个示例，在.NET中新建一个类，并在这个类里新建一个方法，然后在SQL Server中调用这个方法。按照微软所述，通过宿主 Microsoft .NET Framework 2.0 公共语言运行库 (CLR)，SQL Server 2005显著地增强了数据库编程模型。 这使得开发人员可以用任何CLR语言（如C#、VB.NET或C++等）来写存储过程、触发器和用户自定义函数。

我们如何实现这些功能呢？ 
为了使用CLR，我们需要做如下几步： 
1、在.NET中新建一个类，并在这个类里新建一个public方法。 
2、编译这个类为一个DLL。 
3、在SQL Server中注册这个DLL。 
4、新建一个SQL Server函数来访问指定的.NET方法。

接下来，我们一起来完成一个示例 
首先，在Visual Studio中新建一个名为“SQLServerCLRTest”的类库项目。 然后，新建一个名为“CLRFunctions”的类，并在其内添加一个名为“HelloWold”的方法，代码如下： 

复制代码 代码如下:

public class CLRFunctions 
{ 
public static string HelloWorld(string Name) 
{ 
return ("Hello " + Name); 
} 
} 

这是一个非常简单的方法（为了让SQL Server可以调用它，它必须要是public和static的），这个方法有一个string类型的参数，返回信息为“Hello”加上你传入的参数。

现在，我们需要编译这个项目为一个DLL，并在SQL Server中注册它。 这也是比较简单的，在VS中右键单击项目，选择“生成”后程序就会生成一个DLL。 如果你的项目是调试模式的话，那么就可以在如下所示那样的路径里找到编译好的DLL。 

复制代码 代码如下:

C:\Documents and Settings\mark.smith\My Documents\Visual Studio 2005\Projects\SQLServerCLRTest\SQLServerCLRTest\bin\Debug\SQLServerCLRTest.dll 

找到这个DLL后，我们就可以把它拷贝到我们的SQL Server机器上了，如果是相同机器的话我们只要记住这个路径即可。

启用CLR功能 
默认情况下，SQL Server中的CLR是关闭的，所以我们需要执行如下命令打开CLR： 

复制代码 代码如下:

exec sp_configure 'clr enabled',1 
reconfigure 
go 

注册DLL 
为了调用我们写的那个方法，需要在SQL Server中注册我们刚刚编译好的那个DLL。 我们可以在数据库中使用如下命令来注册DLL（路径为你的DLL文件的路径） 

复制代码 代码如下:

CREATE ASSEMBLY asmHelloWorld FROM 'C:\SQLServerCLRTest.dll' 

在SQL Server中调用我们的.NET方法 
为了调用.NET方法，我们可以写一个SQL Server自定义函数，并在其内使用“EXTERNAL NAME”来通知SQL Server使用CLR功能。 代码如下： 

复制代码 代码如下:

CREATE FUNCTION dbo.clrHelloWorld 
( 
@name as nvarchar(200) 
) 
RETURNS nvarchar(200) 
AS EXTERNAL NAME asmHelloWorld.[SQLServerCLRTest.CLRFunctions].HelloWorld 

上面的自定义函数做了两项工作。 首先是声明了一个nvarchar参数，它等同于.NET里的string类型（如果将其设置为varchar并且后面使用了“EXTERNAL NAME”的话就会报错）。然后使用“EXTERNAL NAME”来调用.NET方法。 语法如下： 
程序集名.类名.方法名 
但是，当我使用这个语法调用.NET方法的时候，SQL Server就会报错，所以为了让它正常工作，我使用了如下语法： 
程序集名.[类名].方法名 
现在我们就可以通过如下语句调用.NET方法了： 

复制代码 代码如下:

SELECT dbo.clrHelloWorld('Mark') 

当你运行这段代码的时候，就会得到一个返回结果“Hello Mark”。 

 

注意的问题：

http://www.cnblogs.com/allanhboy/archive/2011/11/10/2244434.html

1.目标数据库实例需要启用clr集成.

如果没启用,可以在 sql server 外围应用配置器 里去启用或者使用以下语句:

sp_configure 'clr enabled', 1
go
reconfigure
go

 

2.目标数据库的可信任属性需要设为false,可以使用以下语句启用:

alter database [dbname] set trustworthy on

否则在调用对应的方式时会报错:

system.security.securityexception: request for the permission of type 'system.security.permissions.environmentpermission, 

mscorlib, version=2.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089' failed.

 

3.确保部署程序集时所使用的账号是目标数据库的所有者,如不是可以使用以下语句:

exec sp_changedbowner 'login_name'

否则部署时会报错:

针对程序集 'xxx' 的 create assembly 失败，因为程序集 'xxx' 未获授权(permission_set = unsafe)。 当符合以下两个条件之一时，将

对程序集授权: 数据库所有者(dbo)具有 unsafe assembly 权限，而且数据库的 trustworthy 属性处于打开状态；或者，程序集签名时所使

用的证书或非对称密钥所对应的登录名具有 unsafe assembly 权限。

当然部署账号需要有创建程序集的权限. 

 

4.程序集的权限级别必须设为 external access,否则在部署的时候会报错:

system.security.securityexception: request for the permission of type 'system.security.permissions.environmentpermission, mscorlib, version=2.0.0.0,culture=neutral, publickeytoken=b77a5c561934e089' failed.

 

5.在项目里增加名为predeployscript.sql和postdeployscript.sql的文件,predeployscript.sql文件里包含以下语句:

if exists (select [name] from sys.assemblies where [name] = n'xxx.xmlserializers') drop assembly [xxx.xmlserializers]

postdeployscript.sql文件包含以下语句:

create assembly [xxx.xmlserializers] from 'e:\xxx.xmlserializers.dll' with permission_set = safe

项目编译成功后,在预生成命令行中加入以下语句:

"d:\program files\microsoft visual studio 8\sdk\v2.0\bin\sgen.exe" /force "$(targetpath)"然后重新编译发布,否则在调用对应的方式时会报错:system.invalidoperationexception: cannot load dynamically generated serialization assembly. in some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. please see inner exception for more information. ---> system.io.fileloadexception: loadfrom()、loadfile()、load(byte[]) 和 loadmodule() 已被主机禁用。 

 

如果还不行就按如下步骤解决：

第一步：

在项目属性中选择“生成事件”选项。然后在“生成后事件命令行”中添加 "D:\Program Files\Microsoft Visual Studio 9.0\SDK\v3.5\Bin\sgen.exe" /force "$(TargetPath)" 命令。调用“sgen.exe”生成序列化程序集，“sgen.exe”文件的位置根据安装的Visual Studio决定。"$(TargetPath)"变量为项目编译后的DLL文件所在路径。在“数据库”选项中的权限级别设置为“不安全”，为了能够访问网络资源。

 

第二步：

在项目文件夹中创建Predeployscript.sql 和 Postdeployscript.sql两个文件。然后在项目菜单中添加现有项，把这两个脚本文件添加到项

目中。

第三步：

将下面的 TRANSACT-SQL 语句添加到 Predeployscript.sql 文件：

IF EXISTS(SELECT [NAME] FROM SYS.ASSEMBLIES WHERE [NAME] = N'GEPSCallWebServiceTrigger.XmlSerializers')
DROP ASSEMBLY [GEPSCallWebServiceTrigger.XmlSerializers]

第四步：

将下面的TRANSACT-SQL 语句添加到Postdeployscript.sql文件：

CREATE ASSEMBLY [GEPSCallWebServiceTrigger.XmlSerializers] 
AUTHORIZATION [dbo]
FROM 'D:\Tmp\CRLTriger\GEPSCallWebServiceTrigger\GEPSCallWebServiceTrigger\bin\Debug\GEPSCallWebServiceTrigger.XmlSerializers.dll'
WITH PERMISSION_SET = UNSAFE 

第五步：

有的时候做完以上几步还是不成功的，因为你的sql没有开启CLR集程，我们要运行以下T-sql代码才可以

use [数据库名]
exec sp_configure 'clr enabled'
exec sp_configure 'clr enabled',1 --开启SQLServer的CLR集程。
reconfigure with override
EXEC   sp_changedbowner '[用户名]' --确保部署程序集时所使用的账号是目标数据库的所有者
Alter Database [数据库名] SET TRUSTWORTHY ON --目标数据库的可信任属性需要设为FALSE 

转载一个有用的，出处不明。