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?
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:
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.
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();
}
}
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.