Error trying to execute sql from inside SQL CLR

I have the following code:

[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;

    }

I get the following exception in

cmd.ExecuteReader();

{"This statement tried to access data whose access is restricted by assembly." }

+5
source share
2 answers

I needed to add DataAccess = DataAccess.Read to a function attribute to do this.

+6
source

As a side note, if you are trying to access temporary tables created with #, for example #tmp, you also need to enter SystemDataAccess = SystemDataAccess.Read. Another way to do this would be to use a common table expression to get your data.

+6
source

All Articles