I have a multi-threaded scheduler process (.NET) that calls a stored procedure that needs to pull data from a Linked Server and eventual insert data into a local table. I'm not explicitly using any transactions--I'm not concerned with the process failing, as I can just rerun. Furthermore, I want to avoid the complication of distributed transactions (MSDTC).
What I'm running into is that most of the time through the .NET process, I am getting the following error:
The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "XXXX" was unable to begin a distributed transaction.
OLE DB provider "SQLNCLI11" for linked server "XXXX" returned message "No transaction is active.
When I execute the stored procedure from SQL Server Management Studio, it does not get the error. I've been trying to track down how the SSMS execution differs from .NET, and I've found some instances were the .NET process does NOT get the error. But even with that, I can't make a distinction between when it fails vs. when it doesn't.
For what it's worth, I put some logging in the procedure to record @@trancount, which is always registering as 2. I can understand one implicit transaction to cover the INSERT statement to save the log, but I cannot account for the second. For what it's worth, my stored procedure actually calls a second procedure which accesses the linked server--not sure if this is relevant. All that said, @@trancount returns 2 even when successful (and regardless if called from SSMS or .NET).
The DBA has changed the linked server properties to ensure "enable promotion of distributed transactions" is false. What else should I try? Thanks!
I discovered what was causing the attempt at a transaction in the first place (which was my goal, since I do not need a transaction). The procedure pulling data from the linked server was inserting that data into a #temp table using the SELECT ... INTO #temp pattern. Since the table it was selecting from was across the linked server, I believe it was creating the temp table there as well. Either that, or it was still creating the temp table locally, but since the selected table was across the linked server, it was trying to extended the transaction (implicit to the insert statement) to cover both local and the linked server.
Either way, when I changed to create the temp table in a separate statement before the INSERT ... SELECT statement, it worked without error!
What this doesn't explain to me is why the procedure would work (every time via SSMS, and rarely via .NET). I'm going to chalk that up to different execution plans somehow... and SQL Server voodoo magic.