For one of the projects I have worked on recently, a set of variables needed to be computed for each product and for each month of the year on a regular basis. The product data was stored in SQL Server 2005 SP2. The computed volume was quite large since around 60 variables x 120 products x 12 months = 86,400 results needed to be calculated every time and be stored in the result table. The business requirements were the following:

  • The calculated variables were based on complex business rules which were quite tricky to be implemented in TSQL.
  • Each variable related to a product had to use input from various tables.
  • Variables related to a given product were independent of other products.
  • On the other hand, variables were sometimes based on others variables for the same product.

It was decided to express the business rules using a .NET language such as C# and to create a Table-Valued Function (TVF) using the CLR. Srinivas Sampath wrote a good introduction on how to implement a TVF in .NET.

The TVF called ufnComputeProduct takes the Product code as the only parameter. The function returns a table with the following columns: the Product code, the VariableName, the MonthID, and the VariableValue. A CROSS APPLY runs all the available products against the function and stores the result set in the VersionedResults table. As mentioned previously, this query would insert around 85,000 records every time. 

INSERT INTO VersionedResults
	(ResultSetVersionID, Product, VariableName, MonthID, VariableValue)
	SELECT @ResultSetVersionID AS ResultSetVersionID,
			Result.Product,
			Result.VariableName, Result.MonthID, Result.VariableValue
	FROM Product
	CROSS APPLY ufnComputeProduct(Products.Product) AS Result

Computing all the variables related to a given product requires to pull various input data from the database. In the .NET code of the user-function, a stored procedure is called in order to return all the relevant input data. The context connection is used since the stored procedure and the CLR function are executed within the same pipe: 

using (SqlConnection connection = new SqlConnection("context connection=true"))
using (SqlCommand sqlCommand = new SqlCommand())           
{
      connection.Open();
      
      // Execute the store procedure
}

Unfortunately, running the CROSS APPLY SQL statement returns the following error: 

Msg 3991, Level 16, State 1, Procedure uspCompute, Line 60
The context transaction which was active before entering user defined routine, trigger or aggregate "ufnComputeProduct" has been ended inside of it, which is not allowed. Change application logic to enforce strict transaction nesting.
The statement has been terminated.

After searching on the Internet, I have discovered that this error message is also related to rollbacks within SQLCLR code (See transaction or trigger rollbacks). On the other hand, running the CROSS APPLY query without the INSERT statement does not raise any error. The problem is therefore related to the fact that the CLR function is called within the transaction scope of the INSERT statement. It turns out that the solution is not to close the context connection: 

// We need to remove the connection out of the using statement
SqlConnection connection = new SqlConnection("context connection=true");

using (SqlCommand sqlCommand = new SqlCommand())           
{
      connection.Open();
      
      // Execute the store procedure
}
// It will work!

Calling methodically Dispose on all the disposable objects which have been created in our code has turned against us. I personally believe that calling Dispose on a context connection should not have any side effect. The SqlConnection implementation should have detected that is was a context connection and should have never closed the connection. The connection was created by SQL Server and should be closed by SQL Server, irregardless of the user code.

One could however argue that calling a stored procedure within a CLR function should not be allowed since calling a stored procedure within a TSQL function is not allowed in SQL Server. However, Herts Chen in an article on SQL Server 2000 mentioned workarounds with OPENQUERY. With SQL Server 2005, using stored procedures within a CLR function can be an acceptable workaround, as long as you are careful with the context connection.