5,388
社区成员
发帖
与我相关
我的任务
分享
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Server;
using System.Data.SqlClient;
namespace SQLLib
{
public class SQLLib
{
[SqlFunction(DataAccess = DataAccessKind.None )]
public static int Encode()
{
//SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=salers;User ID=sa;Password=sa");
int i=0;
using (SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=Test;User ID=sa;Password=sa"))
{
SqlCommand cmd = new SqlCommand("select count(*) as c from t_user", conn);
conn.Open();
SqlDataReader dr= cmd.ExecuteReader();
dr.Read();
i = Convert.ToInt32(dr["c"]);
}
return i;
}
[SqlFunction(DataAccess = DataAccessKind.None)]
public static string aa(string str)
{
return str;
}
}
}
create assembly [SQLLib]
FROM 'H:\SQLLib\SQLLib.dll'
CREATE FUNCTION Encode()
RETURNS int
AS
External name [SQLLib].[SQLLib.SQLLib].Encode
select dbo.Encode()
消息 6522,级别 16,状态 2,第 1 行
在执行用户定义例程或聚合 "Encode" 期间出现 .NET Framework 错误:
System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, 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.PermissionSet.Demand()
at System.Data.Common.DbConnectionOptions.DemandPermission()
at System.Data.SqlClient.SqlConnection.PermissionDemand()
at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at SQLLib.SQLLib.Encode()
。
[SqlFunction(DataAccess = DataAccessKind.None )]
public static int Encode()
{
[SqlFunction(DataAccess = DataAccessKind.Read )]
public static int Encode()
{
------ 已启动生成: 项目: SqlServerProject1, 配置: Debug Any CPU ------
SqlServerProject1 -> F:\test\SqlServerProject1\SqlServerProject1\bin\Debug\SqlServerProject1.dll
------ 已启动部署: 项目: SqlServerProject1, 配置: Debug Any CPU ------
生成启动时间为 2010-12-22 15:48:17。
SqlClrDeploy:
开始将程序集 SqlServerProject1.dll 部署到服务器 .: Test
如果部署的 SQL CLR 项目是为与 SQL Server 目标实例不兼容的 .NET Framework 版本生成的,则可能出现以下错误:“部署错误 SQL01268: 针对程序集的 CREATE ASSEMBLY 失败,因为验证程序集失败”。若要解决此问题,请打开项目的属性,然后更改 .NET Framework 版本。
部署脚本已生成到:
F:\test\SqlServerProject1\SqlServerProject1\bin\Debug\SqlServerProject1.sql
正在创建 [SqlServerProject1]...
F:\test\SqlServerProject1\SqlServerProject1\bin\Debug\SqlServerProject1.sql(39-39): Deploy error SQL01268: .Net SqlClient Data Provider: 消息 6218,级别 16,状态 3,行 1 针对程序集 'SqlServerProject1' 的 CREATE ASSEMBLY 失败,原因是程序集 'SqlServerProject1' 未通过身份验证。请检查被引用程序集是否是最新的,而且是可信的(external_access 或 unsafe),能在该数据库中执行。如果有 CLR Verifier 错误消息,将显示在此消息之后
执行批处理时发生错误。
生成失败。
已用时间 00:00:01.82
========== 生成: 成功或最新 1 个,失败 0 个,跳过 0 个 ==========
========== 部署: 成功 0 个,失败 1 个,跳过 0 个 ==========
EXEC sp_configure 'CLR ENABLE',1
GO
RECONFIGURE
GO
--将UDT导入程序集
CREATE ASSEMBLY ComplexNumbersCS
FROM 'H:\SQLLib\SQLLib.dll'
WITH PERMISSION_SET=SAFE
CREATE FUNCTION Encode()
RETURNS int
AS
External name [SQLLib].[SQLLib]
不懂,帮顶,学习,蹭分.
alter database test set trustworthy on
go
--修改程序集为PERMISSION_SET=EXTERNAL_ACCESS
ALTER ASSEMBLY [SQLLib]
WITH PERMISSION_SET=EXTERNAL_ACCESS;
GO
select dbo.Encode()
--设置数据库选型TRUSTWORTHY 为on
alter database databasename set trustworthy on
go
--修改程序集为PERMISSION_SET=EXTERNAL_ACCESS
ALTER ASSEMBLY SqlClassLibrary
WITH PERMISSION_SET=EXTERNAL_ACCESS;
GO