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?

Solution 1

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.

Solution 2

"SQLCLR does not allow any data access inside TestFillRow" is a mistake.

If you don't use context connection = true connetion string you can access data inside FillRow method.

Comments

[AnthonyWJones]

A very belated acceptance and upvote, (prompted I'm ashamed to say by a down vote on my question). You're absolutely right the nothing runs until Movenext. I've even blogged about this behaviour myself here:- geekswithblogs.net/codingbloke/archive/2010/09/12/… but I clearly didn't see the connection between them until now.

[piers7]

See Connect bug regarding FillRow limitations: connect.microsoft.com/SQLServer/feedback/details/442200/…

[aelveborn]

It is not the problem that the connection opens inside TestFillRow. Even if you provide your own "eager" implementation of an enumerator that opens the connection and fetches the first row inside the main method, that enumerator will not work from TestFillRow because the connection will have been forcefully closed.