1

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!

glancep
  • 356
  • 5
  • 18
  • IIRC, this is either a problem with the LinkedServer definition or with the MSDTC setup on wither the local or remote SQL Server. I'd recommend taking this to hhtp://dba.stackexchange.com where there are more DBAs who might know the details. – RBarryYoung Jul 27 '17 at 17:28
  • Also, this question has a lot of useful information: https://stackoverflow.com/questions/7473508/unable-to-begin-a-distributed-transaction – RBarryYoung Jul 27 '17 at 17:30
  • And here: https://stackoverflow.com/questions/18657768/ole-db-provider-sqlncli-for-linked-server-was-unable-to-begin-a-distributed-tr – RBarryYoung Jul 27 '17 at 17:32
  • 1
    @RBarryYoung: thanks for the replies. I had found both of those Qs--most everything seems to work on how to correct MSDTC. Our DBAs have this disabled and would prefer to keep it that way. Plus, I don't even want a transaction in this instance, so I've just been having a hard time trying to figure out what I'm doing to cause a transaction. I just found what was causing it and will post it as an answer. – glancep Jul 27 '17 at 18:25

1 Answers1

2

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.

glancep
  • 356
  • 5
  • 18