SQL CLR：流表值函数结果

本文介绍了SQL CLR：流表值函数结果的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我的问题是非常相似的这个问题。

不过，我使用SQL Server 2005的Service Pack 2（SP2）（v9.0.3042）和解决方案张贴有没有为我工作。我试着用这两种连接字符串。一个是在我的代码注释掉。

我知道我可以存储在内存中的列表或ArrayList中所有的结果并返回。我已经做了成功，但在这里，不是目的。我们的目标是能够为他们提供流的结果。

这可能使用我的版本的SQL Server？

下面是我的代码：（注意参数实际上并没有被目前使用的我这样做是为了调试）

公共静态类StoredProcs { [SqlFunction（数据访问= DataAccessKind.Read， SystemDataAccess = SystemDataAccessKind。阅读， FillRowMethodName =FillBaseline， TableDefinition =[BASELINE_ID] [INT]，[BASELINE_NAME] [为nvarchar（256），[说明] [为nvarchar（最大），[锁定] [位]）公共静态的IEnumerable fnGetBaselineByID（的SqlString项目名，SqlInt32 baselineID） {串connStr =上下文连接=真正的; //字符串connStr =数据源=;初始目录=数据库名;集成安全= SSPI;争取=假; 使用（SqlConnection的康恩=新的SqlConnection（connStr）） { conn.Open（）;使用（CMD的SqlCommand =新的SqlCommand（的String.Format（@选择* FROM [数据库名]。[DBO]。WITH（NOLOCK） [基线]） ，康涅狄格州）） {使用（SqlDataReader的读卡器= cmd.ExecuteReader（）） {，而（reader.Read（）） {产量返回新的基线（读卡器）; } } } }; } 公共静态无效FillBaseline（obj对象，走出SqlInt32 ID，出来的SqlString名字，出来的SqlString描述，OUT BOOL锁定） {基线基准=（基线）目标文件; ID = baseline.mID; NAME = baseline.nName; 说明= baseline.mDescription; 锁定= baseline.mLocked; } }

下面是我的SQL部署脚本的一部分：

CREATE ASSEMBLY [MyService_Stored_Procs] 从C：\temp\assemblyName.dll'带PERMISSION_SET = 当我使用的连接字符串上下文连接=真正的我得到这个错误 System.InvalidOperationException：>

而从用户定义的表值函数获取新行时发生错误的数据访问是不允许的在。这种上下文。无论是上下文不标明与DataAccessKind.Read或SystemDataAccessKind.Read一个函数或方法，是一个回调从表值函数的方法FillRow获取数据，或者是 UDT 。验证方法

当我用其他的连接字符串我得到这个错误：

而从用户定义的表值函数获取新行时发生错误： System.Security.SecurityException：请求类许可制度.Data.SqlClient.SqlClientPermission，System.Data这，版本2.0.0.0 =文化=中性公钥= b77a5c561934e089'失败。

解决方案

在进一步的研究和反复试验，我发现我的解决方案。我这里提到的文章说

您总成必须PERMISSION_SET = EXTERNAL_ACCESS

这创建要容易得多难啊，但是是一个很好的起点。简单地使用该行的地方PERMISSION_SET =安全的给出了错误：

CREATE组装的AssemblyName'ASSEMBLY失败，因为组装 的AssemblyName'未授权PERMISSION_SET = EXTERNAL_ACCESS。 中的组件授权情况下，下面是正确的：数据库所有者（DBO）具有EXTERNAL ACCESS ASSEMBLY权限和数据库有TRUSTWORTHY数据库属性;或装配与证书或具有相应的与外部访问ASSEMBLY权限登录时的非对称密钥签名。

所以我必须做的第一件事是我签字的dll文件。要做到这一点在Visual Studio 2010中，你去到项目属性，签名选项卡，并选中登录大会，并给它一个名字。对于这个例子，名称为MyDllKey。我选择不使用密码来保护它。然后，当然，我复制dll文件到SQL Server：C：\Temp

使用的this页面作为参考，我创建了使用这些命令3基于上述关键SQL登录：

CREATE ASYMMETRIC KEY MyDllKey从可执行文件=C：\Temp\MyDll.dll'创建LOGIN MyDllLogin从非对称密钥MyDllKey GRANT EXTERNAL ACCESS ASSEMBLY TO MyDllLogin

在登录如上创建的，我现在就可以创建一个使用这个组件：

CREATE ASSEMBLY [MYDLL] 从C：\Temp\MyDll.dll'WITH PERMISSION_SET = EXTERNAL_ACCESS

现在唯一剩下要做的就是使用正确的连接字符串。显然，使用 =招募结合假与连接=真是不可能的。这里是我使用的连接字符串的例子

字符串connStr = @数据源= serverName\instanceName;初始目录=数据库名;集成安全= SSPI;争取=假;

和它的作品！

My issue is very similar to this issue.

However, I'm using SQL Server 2005 Service Pack 2 (SP2) (v9.0.3042) and the solution posted there does not work for me. I tried using both connection strings. One is commented out in my code.

I realize I can store all the results in a List or ArrayList in memory and return that. I've done that successfully, but that is not the goal here. The goal is to be able to stream the results as they are available.

Is this possible using my version of SQL Server?

Here's my code : (Note that the parameters aren't actually being used currently. I did this for debugging)

public static class StoredProcs { [SqlFunction( DataAccess = DataAccessKind.Read, SystemDataAccess=SystemDataAccessKind.Read, FillRowMethodName="FillBaseline", TableDefinition = "[baseline_id] [int], [baseline_name] [nvarchar](256), [description] [nvarchar](max), [locked] [bit]" )] public static IEnumerable fnGetBaselineByID(SqlString projectName, SqlInt32 baselineID) { string connStr = "context connection=true"; //string connStr = "data source=.;initial catalog=DBName;integrated security=SSPI;enlist=false"; using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); using (SqlCommand cmd = new SqlCommand(String.Format(@" SELECT * FROM [DBName].[dbo].[Baseline] WITH (NOLOCK) "), conn)) { using (SqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { yield return new Baseline(reader); } } } }; } public static void FillBaseline(Object obj, out SqlInt32 id, out SqlString name, out SqlString description, out bool locked) { Baseline baseline = (Baseline)obj; id = baseline.mID; name = baseline.nName; description = baseline.mDescription; locked = baseline.mLocked; } }

Here's part of my SQL deploy script:

CREATE ASSEMBLY [MyService_Stored_Procs] FROM 'C:\temp\assemblyName.dll' WITH PERMISSION_SET = SAFE

When I use the connection string "context connection=true" I get this error:

An error occurred while getting new row from user defined Table Valued Function : System.InvalidOperationException: Data access is not allowed in this context. Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method.

When I use the other connection string I get this error:

An error occurred while getting new row from user defined Table Valued Function : 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.

解决方案

Upon further research and trial and error I found my solution. The article that I mentioned here says

your assembly must be created with permission_set=external_access

This is much easier said than done, but was a good starting point. Simply using that line in place of permission_set=safe gives the error:

CREATE ASSEMBLY for assembly 'assemblyName' failed because assembly 'assemblyName' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.

So the first thing I had to do was sign my dll file. To do that in Visual Studio 2010, you go to the project properties, Signing tab, and check "Sign the assembly" and give it a name. For this example, the name is MyDllKey. I chose not to protect it with a password. Then, of course, I copied the dll file to the sql server: C:\Temp

Using this page as a reference, I created a SQL login based on the above key using these 3 commands:

CREATE ASYMMETRIC KEY MyDllKey FROM EXECUTABLE FILE = 'C:\Temp\MyDll.dll' CREATE LOGIN MyDllLogin FROM ASYMMETRIC KEY MyDllKey GRANT EXTERNAL ACCESS ASSEMBLY TO MyDllLogin

Once the login is created as above, I can now create the assembly using this:

CREATE ASSEMBLY [MyDll] FROM 'C:\Temp\MyDll.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS

Now the only thing left to do is use the proper connection string. Apparently using enlist=false in combination with connection=true is not possible. Here is an example of the connection string I used.

string connStr = @"data source=serverName\instanceName;initial catalog=DBName;integrated security=SSPI;enlist=false";

And it works!