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
MonthID, and the
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.
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:
Unfortunately, running the
CROSS APPLY SQL statement returns the following error:
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
It turns out that the solution is not to close the context connection:
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
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.