Getting data inside CLR Procedure from different server

Question

I have a CLR Procedure that opens a connection to a server, let's say SERVER2, and gets some data, processes it and returns a table.

[SqlFunction(
   DataAccess = DataAccessKind.Read,
   FillRowMethodName = "List_FillRow",
   TableDefinition = "Date datetime, Quantity int")]
public static IEnumerable ListCount(...)
{
  using (SqlConnection con = new SqlConnection(connectionStringToSERVER2))
  { ... }
  return list;
}

This CLR Procedure is run from an SQL Stored Procedure that runs on SERVER1.

CREATE PROCEDURE [dbo].[sp_Reports_NumaraSpalariJetWash]
BEGIN
    DECLARE curs CURSOR
    LOCAL STATIC READ_ONLY FORWARD_ONLY
    FOR   

    SELECT ...

    OPEN curs  

    FETCH NEXT FROM curs   
    INTO ...;

    WHILE @@FETCH_STATUS = 0  
    BEGIN

        INSERT INTO ...
                    (...)
        SELECT Date, Quantity
        FROM            dbo.ListCount(...)

        FETCH NEXT FROM curs   
        INTO ...;
    END
END

I get the errors: "System.Data.SqlClient.SqlException: Transaction context in use by another session." In results window this error appears multiple times, I guess once for every loop into FETCH.

Answer 1

Doing an INSERT...SELECT should result in a system-generated Transaction to bind the INSERT and SELECT together as an atomic operation. The default behavior of SqlConnection, starting in .NET 4.0 or 4.5 is for Enlist to be True whereas it had defaulted to False in prior versions of the .NET Framework. When Enlist is True, the new connection will attempt to join the existing Transaction.

Try adding Enlist=False; to the Connection String.

For more info on working with SQLCLR in general, please visit SQLCLR.org.