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

问题描述

我创建了使用CLR触发器连接到WCF服务器并通知数据库更改的系统。
它可以在SQL Server 2008 R2上正常运行。
现在我正在尝试在SQL Server 2012上进行迁移。
要使用WCF，我需要将SMDiagnostics.dll程序集与其他程序一起加载。
我已经检查了db中是否启用了clr，并将可信任设置为 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:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication        Foundation\SMdiagnostics.dll'
with permission_set = unsafe
go

我收到以下错误

警告：Microsoft .NET Framework程序集的smdiagnostics，
版本= 3.0.0.0，文化=中性，publickeytoken = b77a5c561934e089，
processorarchitecture = 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程序集的依赖项。微软已经向我们表明，尽管这在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屋！