Error trying to execute sql from inside SQL CLR

Question

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 has attempted to access data whose access is restricted by the assembly."}

this is not an issue anymore, I resolved it !

Pacman
– Pacman

2013-04-02 18:22:04 +00:00
Commented Apr 2, 2013 at 18:22 — Pacman
– Pacman, Commented Apr 2, 2013 at 18:22

Derreck Dean · Accepted Answer · 2013-12-13 21:14:04Z

10

As a side note, if you're trying to access temporary tables created with #, such as #tmp, you need to also put in SystemDataAccess = SystemDataAccess.Read. Another way around this would be to use a common table expression to get your data.

answered Dec 13, 2013 at 21:14

Derreck Dean

3,7661 gold badge28 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pacman · Accepted Answer · 2013-04-02 18:23:02Z

7

I needed to add DataAccess = DataAccess.Read to the function attribute in order to do that.

answered Apr 2, 2013 at 18:23

Pacman

2,2457 gold badges42 silver badges72 bronze badges

Collectives™ on Stack Overflow

Error trying to execute sql from inside SQL CLR

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related