SqlFunction fails to open context connection despite DataAccessKind.Read present

Question

I've got a SqlServer project with a very simple test for a Table-Valued-Function:-

[SqlFunction(TableDefinition = "forename nvarchar(50)", FillRowMethodName = "TestFillRow", DataAccess = DataAccessKind.Read)]
public static IEnumerable TestConn(int ID)
{
 using (SqlConnection con = new SqlConnection("context connection=true"))
 {
  //con.Open();
  yield return "Anthony";
 }
}

public static void TestFillRow(object obj, out string forename)
{
 forename = (string)obj;
}

Note the Open on the connection is currently commented out. Once deployed I can execute like this in SQL:-

SELECT * FROM [dbo].[TestConn](1)

All works fine.

Now I uncomment the con.open() and it fails with:-

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.

I don't see what the problem is, the TestConn function has got DataAccessKind.Read.

Anyone know of any other reasons for getting this error?

Answer 1

I have not worked on SQLCLR.
And, I looked up the docs & searched a few sites.

Why do you need conn.Open()?
I mean, you will have the context connection available to you (it is open as part of a call to function).

See if this link helps.

EDIT: If you found the solution to this, post it here. It will help other people facing similar problem.

Answer 2

The problem is the following:

1) SQLCLR does not allow any data access inside TestFillRow

2) Even thouth 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.

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

Good luck.