sqlclr - 尝试从 SQL CLR 内部执行 sql 时出错

Question

我有以下代码:

[SqlFunction(DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read)]
    public static int GetInt()
    {
        int retValue = 0;
        using (SqlConnection conn = new SqlConnection("context connection = true"))
        {
            conn.Open();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "select MyInt from SomeTable";
            object timeOut = cmd.ExecuteReader(); // <- error happen here

        }
        return retValue;

    }

我得到以下异常

cmd.ExecuteReader();

{"This statement has attempted to access data whose access is restricted by the assembly."}

Answer 1

附带说明一下，如果您尝试访问使用 # 创建的临时表，如 #tmp ，您还需要输入SystemDataAccess = SystemDataAccess.Read .解决此问题的另一种方法是使用公用表表达式来获取数据。