最近要用到CLR,遇到一些问题,先将解决方法记下,以备后用。
1. 环境:Sql Server 2005 、.NET Framework 3.52. 问题:当ASSEMBLY 的PERMISSION_SET=SAFE 时,在调用时发生如下错误:
A .NET Framework error occurred during execution of user-defined routine or aggregate "siemens_TaskReminder":
System.Security.SecurityException: Request for the permission of type 'System.Net.WebPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.System.Security.SecurityException: at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet) at System.Security.CodeAccessPermission.Demand() at System.Net.HttpWebRequest..ctor(Uri uri, ServicePoint servicePoint) at System.Net.HttpRequestCreator.Create(Uri Uri) at System.Net.WebRequest.Create(Uri requestUri, Boolean useUriBase) at Siemens.ASLeadership.CLRTaskReminder.StoredProcedures.TaskReminder(String checkUrl)3. 解决方法:
将PERMISSION_SET 改为 EXTERNAL_ACCESS。前提要预先执行如下语句:
语句 ALTER DATABASE [Database] SET TRUSTWORTHY ON 尤为重要。
View Code
1 EXEC SP_CONFIGURE 'CLR ENABLED',1 --开启SQLSERVER的CLR集程。2 RECONFIGURE WITH OVERRIDE3 EXEC SP_CHANGEDBOWNER 'SA' --确保部署程序集时所使用的账号是目标数据库的所有者4 ALTER DATABASE [Database] SET TRUSTWORTHY ON --目标数据库的可信任属性需要设为FALSE5 exec SP_DBCMPTLEVEL 'Database',906 GO