Linked servers SQLNCLI problem. “No transaction is

Question

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 1

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.

Answer 2

Have you tried using openquery?

insert into table select * from openquery(myservername, 'exec mydatabase.dbo.mystoredproc param1, param2, param3')

Answer 3

You also have to check the DNS name resolution in the IP network configuration.

For example, you have a server called server-a.mydomain.com and another one called server-b.otherdomain.com, log in the server-a and do a "ping server-b" (without the domain).

If it responds "Ping request could not find host server-b. Please check the name and try again." that is the problem.

Go to the Control Pannel > Network Connections > Right click in the network card > properties > Internet Protocol > Properties > Advanced > DNS > Append this DNS suffix in order. And here add the local domain: mydomain.com and then add the remote domain: otherdomain.com Click OK until you exit

Now if you do the "ping server-b" it should repond something like:

Pinging server-b.otherdomain.com [192.168.1.2] with 32 bytes of data: Reply from 192.168.1.2: bytes=32 time=12ms TTL=64 Reply from 192.168.1.2: bytes=32 time=9ms TTL=64

Now try to again to execute the distributed transaction.