SQL CLR: Streaming table valued function results

My iss开发者_StackOverflow社区ue is very similar to this issue.

However, I'm using SQL Server 2005 Service Pack 2 (SP2) (v9.0.3042) and the solution posted there does not work for me. I tried using both connection strings. One is commented out in my code.

I realize I can store all the results in a List or ArrayList in memory and return that. I've done that successfully, but that is not the goal here. The goal is to be able to stream the results as they are available.

Is this possible using my version of SQL Server?

Here's my code : (Note that the parameters aren't actually being used currently. I did this for debugging)

public static class StoredProcs
{
    [SqlFunction(
        DataAccess = DataAccessKind.Read,
        SystemDataAccess=SystemDataAccessKind.Read,
        FillRowMethodName="FillBaseline",
        TableDefinition = "[baseline_id] [int], [baseline_name] [nvarchar](256), [description] [nvarchar](max), [locked] [bit]"
        )]
    public static IEnumerable fnGetBaselineByID(SqlString projectName, SqlInt32 baselineID)
    {
        string connStr = "context connection=true";
        //string connStr = "data source=.;initial catalog=DBName;integrated security=SSPI;enlist=false";
        using (SqlConnection conn = new SqlConnection(connStr))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand(String.Format(@"
                SELECT *
                FROM [DBName].[dbo].[Baseline] WITH (NOLOCK)
            "), conn))
            {
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        yield return new Baseline(reader);
                    }
                }
            }
        };
    }

    public static void FillBaseline(Object obj, out SqlInt32 id, out SqlString name, out SqlString description, out bool locked)
    {
        Baseline baseline = (Baseline)obj;
        id = baseline.mID;
        name = baseline.nName;
        description = baseline.mDescription;
        locked = baseline.mLocked;
    }
}

Here's part of my SQL deploy script:

CREATE ASSEMBLY [MyService_Stored_Procs]
FROM 'C:\temp\assemblyName.dll'
WITH PERMISSION_SET = SAFE

When I use the connection string "context connection=true" I get this error:

An error occurred while getting new row from user defined Table Valued Function : System.InvalidOperationException: 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.

When I use the other connection string I get this error:

An error occurred while getting new row from user defined Table Valued Function : System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

Upon further research and trial and error I found my solution. The article that I mentioned here says

your assembly must be created with permission_set=external_access

This is much easier said than done, but was a good starting point. Simply using that line in place of permission_set=safe gives the error:

CREATE ASSEMBLY for assembly 'assemblyName' failed because assembly 'assemblyName' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.

So the first thing I had to do was sign my dll file. To do that in Visual Studio 2010, you go to the project properties, Signing tab, and check "Sign the assembly" and give it a name. For this example, the name is MyDllKey. I chose not to protect it with a password. Then, of course, I copied the dll file to the sql server: C:\Temp

Using this page as a reference, I created a SQL login based on the above key using these 3 commands:

CREATE ASYMMETRIC KEY MyDllKey FROM EXECUTABLE FILE = 'C:\Temp\MyDll.dll'
CREATE LOGIN MyDllLogin FROM ASYMMETRIC KEY MyDllKey
GRANT EXTERNAL ACCESS ASSEMBLY TO MyDllLogin

Once the login is created as above, I can now create the assembly using this:

CREATE ASSEMBLY [MyDll]
FROM 'C:\Temp\MyDll.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS

Now the only thing left to do is use the proper connection string. Apparently using enlist=false in combination with connection=true is not possible. Here is an example of the connection string I used.

string connStr = @"data source=serverName\instanceName;initial catalog=DBName;integrated security=SSPI;enlist=false";

And it works!

The original problem is due to use of the yield keyword within your function, as explained in this question: SqlFunction fails to open context connection despite DataAccessKind.Read present.

If you avoid using yield (store results in an intermediate array, return the whole lot at the end) the problem goes away.

Alternatively you can do as you describe and avoid using the context connection, but if you do that you have to mark your assembly for external access as you describe. I think that's best described as a workaround, rather than a solution, given you lose some of the benefits available from a context connection and because of all the extra hoops you have to jump through.

In many cases the benefit of being able to use streaming behaviour (yield) does outweigh this pain, but it's still worth considering both options.

Here's the bug on Connect: http://connect.microsoft.com/SQLServer/feedback/details/442200/sql-server-2008-clr-tvf-data-access-limitations-break-existing-code

Googling for this:

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.

Led me to this page, but without the answer I needed.
I eventually figured out what it was.
In my CLR Function, I was calling another Method and passing in the values the Function had recieved.

Sounds innocuous, but what I had done was used the same datatypes (SqlChars, SqlBoolean, SqlInt32) for the input-parameters of the Method I added.

private static ArrayList FlatFile(SqlChars Delimeter, SqlChars TextQualifier)

Apparently using these datatypes for anything other than a CLR SqlFunction or SqlProcedure can sometimes give you this type of cryptic error.

Once I removed those datatypes on my new Method and used the C# ones (string, bool, int), the error finally went away.

private static ArrayList FlatFile(string Delimeter, string TextQualifier)

NOTE: This only errored out when I was using Impersonation to grab a file from another Domain.
When I streamed the file over the local Domain, I didn't receive this error, which is what threw me off.

I hope this helps you in your time of need. I blew way too many hours troubleshooting this.