CLR 自定义函数调试错误 DataAccessKind.Read or SystemDataAccessKind.Read

调试CLR函数，基本格式简写如下：

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    
    [Microsoft.SqlServer.Server.SqlFunction]
    public static string f_testfunc(string encrstr)
    {
        string output = "00";
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            conn.Open();
        }  
        return output;
    }
};

错误如下：

A .NET Framework error occurred during execution of user-defined routine or aggregate "f_testfunc": 
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.

System.InvalidOperationException: 
   在 System.Data.SqlServer.Internal.ClrLevelContext.CheckSqlAccessReturnCode(SqlAccessApiReturnCode eRc)
   在 System.Data.SqlServer.Internal.ClrLevelContext.GetCurrentContext(SmiEventSink sink, Boolean throwIfNotASqlClrThread, Boolean fAllowImpersonation)
   在 Microsoft.SqlServer.Server.InProcLink.GetCurrentContext(SmiEventSink eventSink)
   在 Microsoft.SqlServer.Server.SmiContextFactory.GetCurrentContext()
   在 System.Data.SqlClient.SqlConnectionFactory.GetContextConnection(SqlConnectionString options, Object providerInfo, DbConnection owningConnection)
   在 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
   在 System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
   在 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   在 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   在 System.Data.SqlClient.SqlConnection.Open()
   在 UserDefinedFunctions.f_testfunc(String encrstr)
   

错误及原因：

function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read

The problem is the following:

1. SQLCLR does not allow any data access inside TestFillRow

2. Even though it "looks" like your TestFillRow doesnt access data, the way the compiler translates code with "yield" statements is by actually deferring it's execution until the first .MoveNext() call to the iterator. Therefore the following statement:

   using (SqlConnection con = new SqlConnection("context connection=true"))        

   gets executed inside TestFillRow... which is illegal.

Do not use yield return; instead load the whole result to a List<> and return the list at the end of the UD Function.

（ 参考： SqlFunction fails to open context connection despite DataAccessKind.Read present）

解决方法：

[Microsoft.SqlServer.Server.SqlFunction]  改为 [SqlFunction(DataAccess = DataAccessKind.Read)]

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    
    //[Microsoft.SqlServer.Server.SqlFunction]
    [SqlFunction(DataAccess = DataAccessKind.Read)] //更改这里
    public static string f_testfunc(string encrstr)
    {
        string output = "00";
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            conn.Open();
        }  
        return output;
    }
};

参考：CLR 标量值函数