Insert into table.. exec at linked server does not work

This works, returning a resultset :

exec ('select ''col'', count(1) from test.dbo.[Table1] with (nolock)开发者_运维技巧') at svrA

When I try to insert the resultset into a table:

insert into rowcount_sub (tablename,rowcnt)
exec ('select ''col'', count(1) from test.dbo.[Table1] with (nolock)') at svrA

Fails giving this error:

OLE DB provider "SQLNCLI10" for linked server "svrA" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "svrA" was unable to begin a distributed transaction.

---

If you do not use distributed transaction on purpose, You can use advanced properties of the linked server object on main server to disable promotion of distributed transaction.

---

I was able to solve the same problem by using OPENQUERY instead of EXEC:

insert into rowcount_sub (tablename,rowcnt)
SELECT * FROM OPENQUERY(svrA, 'select ''col'', count(1) from test.dbo.[Table1] with (nolock)')

Hope it helps someone...

---

The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "svrA" was unable to begin a distributed transaction.

The message is pretty clear and quite explicit. All you have to do is open your system documentation and follow the steps on configuring distributed transactions: Configuring MS DTC Services.

There are also plenty of blogs and tutorials out there:

• How to configure DTC on Windows 2003
• How to configure DTC on Windows 2008

---

Changing "Enable Promotion of Distributed Transaction" from True to false fixed my issue.