MSSQL 2012 为 WCF 创建 CLR 触发器失败 [英] MSSQL 2012 creating CLR triggers for WCF fails

查看:30
本文介绍了MSSQL 2012 为 WCF 创建 CLR 触发器失败的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我创建了使用 CLR 触发器连接到 WCF 服务器并通知它有关数据库更改的系统.它在 SQL Server 2008 R2 上运行正常.现在我正在尝试在 SQL Server 2012 上进行迁移.要使用 WCF,我需要加载 SMDiagnostics.dll 程序集以及其他程序集.我检查了 clr 在 db 中启用,并将 trustworthy 设置为on",我禁用了 WCF 调试,我检查了 SQL Server 在本地系统帐户下运行,因此权限没有问题.现在我的问题是当我运行以下命令时

I've created system that uses CLR triggers to connect to WCF server and notify it about changes in DB. It runs ok on SQL server 2008 R2. Now im trying to migrate on SQL Server 2012. To use WCF i need to load SMDiagnostics.dll assembly along the others. Ive checked that clr is enabled in db , and set trustworthy to be "on", ive disabled WCF debuging, ive checked that SQL server runs under Local System account so there is no problems with permissions. Now my problem is that when i run following command

IF  NOT EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'SMdiagnostics')
create assembly [SMdiagnostics]
from  'C:WindowsMicrosoft.NETFrameworkv3.0Windows Communication        FoundationSMdiagnostics.dll'
with permission_set = unsafe
go

我收到以下错误

警告:Microsoft .NET Framework 程序集的 smdiagnostics,版本=3.0.0.0,文化=中性,publickeytoken=b77a5c561934e089,处理器架构=msil.'您注册的未经过全面测试在 SQL Server 托管环境中,不受支持.在里面将来,如果您升级或维护此程序集或 .NET Framework,您的 CLR 集成例程可能会停止工作.请参考 SQL Server在线书籍了解更多详情.消息 6586,级别 16,状态 1,第 2 行无法安装程序集SMdiagnostics",因为存在政策会阻止它被使用.

Warning: The Microsoft .NET Framework assembly 'smdiagnostics, version=3.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details. Msg 6586, Level 16, State 1, Line 2 Assembly 'SMdiagnostics' could not be installed because existing policy would keep it from being used.

SMdiagnostics.dll 存在于指定路径.据我了解它在 SQL Server 2012 或 GAC 中的某种策略,但是我找不到关于 SMdiagnostics 的任何策略.任何想法如何解决它?谢谢.

SMdiagnostics.dll exists at specified path. As i understand its some kind of policy in SQL server 2012 or in GAC, however i cant find no policies about SMdiagnostics. Any ideas how to solve it? Thanks.

推荐答案

我们一个月前就同一问题向 Microsoft 提交了一个问题.我的猜测是您无法加载 System.IdentityModel.dll,它是 System.ServiceModel 程序集的依赖项.Microsoft 已向我们表示,虽然这在 SQL 2005 和 SQL 2008 中有效,但它是 SQL 2012 中的已知错误,并且他们不会在 SQL 2014 之前修复它.

We submitted an issue with Microsoft a month ago on the same problem. My guess is you are unable to load System.IdentityModel.dll which is a dependency of the System.ServiceModel assembly. Microsoft has indicated to us that while this worked in SQL 2005 and SQL 2008 that it is a known bug in SQL 2012 and they are not going to fix it till SQL 2014.

这一事实尚未广为人知,这对我来说有点意外,只是 2012 年仍然很新.但这意味着您不能使用我所说的 Microsoft 在 SQL CLR (WCF) 中的最佳实践进程间通信技术,请注意 .NET 远程处理也会被淘汰,因为它也使用 ServiceModel 程序集.

The fact that this has not become widely known yet is a bit of a surprise to me except that 2012 is still very new. But this means that you cannot use what I would say is Microsoft's best practice Interprocess Communication technology within SQL CLR (WCF), note that .NET remoting would be out also because it also uses the ServiceModel assembly.

我希望对他们说我们应该改用什么进行更详细的说明,我正在研究是否有一种方法可以编写基于 WSE 的 SQL CLR,但对前景并不十分兴奋.

I am hoping for greater clarification on what they would say we should use instead, I am looking into if there is a way to write a WSE based SQL CLR, but not very excited about the prospect.

我对此很不高兴,希望其他人提高他们的声音,指出这是一个真正的问题,应该被认为是不可接受的.

I'm quite unhappy about this and hope that others raising their voice will point out that it is a real problem and should be considered unacceptable.

这篇关于MSSQL 2012 为 WCF 创建 CLR 触发器失败的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆