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 row

Solution 1:

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

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

Hope it helps someone...

Solution 2:

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.

Solution 3:

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

Solution 4:

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