SQL Server 2005 CLR 调用Web Service需要注意的几个问题

用visual studio 创建sql server项目,然后编写调用web service的过程是很简单的,在项目里添加引用web service,然后编写处理代码.在部署前有几个问题需要注重:

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 ''sa'' --确保部署程序集时所使用的账号是目标数据库的所有者
Alter Database [数据库名] SET TRUSTWORTHY ON --目标数据库的可信任属性需要设为FALSE