Paste into table .. exec on linked server not working

Question

This works by returning a result set:

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

When I try to insert a result set into a table:

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

Failed to execute 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.

Answer 1

The operation could not be completed because the OLE DB provider "SQLNCLI10" for the linked server "svrA" could not start the distributed transaction.

The message is pretty clear and pretty explicit. All you have to do is open the system documentation and follow the steps for setting up distributed transactions: "Configuring MS DTC services" .

There are also many blogs and tutorials:

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

Answer 2

, OPENQUERY EXEC:

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

, -...

Answer 3

If you are not using a distributed transaction specifically, you can use the advanced properties of the linked server object on the main server to disable the promotion of the distributed transaction.

Answer 4

Changing "Enable distributed transaction promotion" from True to false fixed my problem.