What are the limitations of a CLR context connection.

Question

I'm trying to convert some existing C# code into a CLR stored procedure. The obvious connection string to use is a context connection ("context connection = true").

The problem I'm running into is some things apparently don't work on this connection type. The latest thing is the SqlConnection.GetSchema method fails. The error just says it is not available on a context connection. Is there a list somewhere of things that won't work on a context connection?

Answer 1

You might have already come across this in the MSDN SQL Server 2008 Books Online, however, the following link explains the restrictions imposed when using Context Connections:

Restrictions on Regular and Context Connections

If you post the full exception and stack trace then it may be possible to ascertain from the .NET FX assemblies (using .NET Reflector), or those deployed with SQL 2005/2008, why this exception is thrown.

HTH
Kev

Answer 2

After some more seaching I found this list of things that won't work with a context connection here. Some of these are also documented in the SQL Books link above but it doesn't mention them all.

Things that don't work with a context connection.

• ChangePassword method
• GetSchema method
• Connection pooling and associated parameters and methods
• Transparent failover when database mirroring is used
• Client statistics PacketSize, WorkstationID, and other client information

Answer 3

Someone might find this useful:

    System.Data.DataTable schema = null;
    using (System.Data.SqlClient.SqlCommand cmd = database.Connection.CreateCommand())
    {
        cmd.CommandText = string.Format( "SELECT TOP 1 * FROM {0}", mainTable );
        cmd.CommandType = System.Data.CommandType.Text;
        using (System.Data.SqlClient.SqlDataReader rdr = cmd.ExecuteReader())
        {
            schema = rdr.GetSchemaTable();
        }
    }