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

Question

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.

Answer 1

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.