Linked servers SQLNCLI problem. "No transaction is active"
Im trying to execute a stored procedure and simply insert its results in a temporary table, and I'm getting the following message:
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "MyServerName" was unable to begin a distributed transaction. OLE DB provider "SQLNCLI" for linked server "MyServerName" returned message "No transaction is active.".
My query looks like this:
INSERT INTO #TABLE
EXEC MyServerName.MyDatabase.dbo.MyStoredProcedure Param1, Param2, Param3
Exact column number, names, the problem is not the result.
MSDTC is allowed and started in both computers, Remote procedure calling too.
The machines are not in the same domain, but I can execute remote queries from my machine and get the result. I can even execute the stored procedure and see its results, I just can't insert it in another table.
EDIT
Oh I forgot to mention, the stored procedure doesn't fire any trigger. It only inserts records in temporary tables which it creates itself for data treating.
Answer
Well, after following lots of tutorials and researching a lot about it, I had changed all the configuration I thought was necessary for it to work, but it still didn't.
Today we had to force a power reboot on our development server because of a faulty no-break, and when we booted up the server, guess what? It works!
So just for the record, I've changed some specific MSDTC configuration, added it as a linked server and allowed RPC IN and OUT, and changed the RPC configuration for 'NO AUTHENTICATION REQUIRED' or something like that.
I remember reading somewhere that after you changed this configuration, a reboot was required, even though Windows says that it has already restarted the service.
I had rebooted my server like... twice since I changed it, and it still didn't work. But as today, after a complete turn off and turn on, it works!
As for the syntax, I kept the same.