SQL 2012 CLR error: "The context transaction which was active before entering ..."?

I am experience strange error with SQL 2012 and CLR:


The context transaction which was active before entering user defined
routine, trigger or aggregate "My routine" has been ended inside of
it, which is not allowed. Change application logic to enforce strict
transaction nesting.

I have no idea what can be the problem. Can anyone give me some direction where to search?

In general I have SQL Stored procedure inside which in UPDATE Clause I call scalar CLR function. The function gets 2 xml strings: one for xml and other for xsd an make validation. The function is called many times in the query.

EDIT:

ERROR message:


Error Number: 3991


Error Severity: 16


Error State: 1


Error Procedure: sproc_XXX


Error Line: 297


Error Message: The context transaction which was active before
entering user defined routine, trigger or aggregate "CLR_XXX_Xml" has
been ended inside of it, which is not allowed. Change application
logic to enforce strict transaction nesting.

EDIT 2:

I find the problem. When we used CLR to validate XML, we sned 1st parameter to be the XSD name. Then in the CLR we extract XSD schema from SQL Server. In our case we have empty string for XSD name, but even we have try / catch the error is passed upwards the SQL Server stored procedure.

Our function returns 0 / 1 so the recors with 0 must be updated with error code.

But in this case we receive exception in SQL.

My code is:

SQL for Update

```
UPDATE FR
SET ErrorCode = 'OUR ERROR CODE'
FROM dbo.DataTable FR
INNER JOIN dbo.TempTable FRN ON (FRN.SessionGUID = @sSessionGUID)
AND (FRN.IDLog = FR.IDLog)
AND (FRN.UniqueID = FR.UniqueID)
AND (
(FRN.Template LIKE '%##%')
OR (PANI_Core.dbo.funCLRs_CheckXML('schRPT_' + @sSchema + '_Peliminary',
FRN.Template) <> 1)
OR (NULLIF(FRN.Template, '') IS NULL)
OR (
(TRY_CONVERT(DATE, FRN.ExpirationDate) IS NULL)
AND (N

The direct cause of the "The context transaction which was active before entering user defined routine, trigger or aggregate "..." has been ended inside of it, which is not allowed." error is:

You are calling the XML_SCHEMA_NAMESPACE() built-in function, and sometimes it fails due to not finding the specified XML Schema Collection. As per the info in the Question, you are passing in an empty string when this error happens, though passing in any string that doesn't match an existing XML Schema Collection would cause this error.

What is happening is you are currently in a Transaction when this SQLCLR function is called (i.e. the Transaction of the UPDATE statement), and the XML_SCHEMA_NAMESPACE failing is a batch-aborting error, in which case the Transaction is automatically rolled-back (hence the try...catch in the .NET code can't help here). This is really the same error you would get if you had nested Stored Procedure calls where the outer Stored Procedure began a Transaction and the inner Stored Procedure issued a ROLLBACK in which case the value of @@TRANCOUNT would be lower upon exiting the inner Stored Procedure than it was upon entering it, and you would get an error stating that the starting and ending values of @@TRANCOUNT were not the same.

Here is example code showing that XML_SCHEMA_NAMESPACE failing is a batch (and hence Transaction) aborting error:

BEGIN TRAN;
PRINT @@TRANCOUNT;
DECLARE @SchemaCollectionName sysname = N'f';
PRINT CONVERT(NVARCHAR(4000), XML_SCHEMA_NAMESPACE(N'dbo', @SchemaCollectionName));
GO
PRINT @@TRANCOUNT;
ROLLBACK;


Returns:


1

Msg 6314, Level 16, State 1, Line 4

Collection specified does not exist in metadata : 'f'

0

Msg 3903, Level 16, State 1, Line 7

The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

The root cause of this error is:

You are allowing values to be passed to the XML_SCHEMA_NAMESPACE function that cause it to error, specifically an empty string.

The fix for this error is to do the following:

• Also test for NULL and white-space-only string values. This will prevent errors due to NULL values (which would cause an error in your current code anyway) as well as non-empty strings filled with any combination of spaces, tabs, returns, etc. (the IsNullOrWhiteSpace method checks for all Unicode white-space characters). Replace:

if (XsdSchemaString.ToString().Length == 0)

with:

if (XsdSchemaString.IsNull || String.IsNullOrWhiteSpace(XsdSchemaString.Value))

-
Do an existence check for the XML Schema Collection name being passed in, prior to calling XML_SCHEMA_NAMESPACE so that you only ever call that function if the name actually exists. You can do this in the same step as the current SELECT by wrapping that in an IF EXISTS:

DECLARE @SchemaCollectionName NVARCHAR(128) = N'not here';
IF (EXISTS(
        SELECT  *
        FROM        sys.xml_schema_collections
        WHERE   [schema_id] = SCHEMA_ID(N'dbo')
        AND     [name] = @SchemaCollectionName
        )
    )
BEGIN
    SELECT XML_SCHEMA_NAMESPACE(N'dbo', @SchemaCollectionName) AS [MyXSD];
END;

This will prevent errors due to incorrect non-empty, non-white-space-only values. An incorrect value will not return a result set, and your code already handles that as the else condition for if (reader.Read()).

• While not critical to this issue, it would be best if you parameterized that query. The example code in both places show that the function does accept a variable for the XML Schema Collection name.

StackExchange Database Administrators Q#128695, answer score: 2