June 26, 2018 at 2:23 pm
I have no problem running this via linked Server, and get a good result set
EXECUTE [192.168.xx.xx].master.dbo.xp_sqlagent_enum_jobs 1,''
But when I try to insert the results into a local table with this
INSERT INTO MyDB..currently_running_jobs
EXECUTE [192.168.xx.xx].master.dbo.xp_sqlagent_enum_jobs 1,''
I get this error:
OLE DB provider "SQLNCLI11" for linked server "192.168.xx.xx" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 69
The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "192.168.xx.xx" was unable to begin a distributed transaction.
Inserting from Local jobs works fine, just not from Linked server as above.
INSERT INTO MyDB..currently_running_jobs
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,''
June 26, 2018 at 2:44 pm
Is DTC enabled on both servers?
June 26, 2018 at 2:47 pm
homebrew01 - Tuesday, June 26, 2018 2:23 PMI have no problem running this via linked Server, and get a good result setEXECUTE [192.168.xx.xx].master.dbo.xp_sqlagent_enum_jobs 1,''
But when I try to insert the results into a local table with this
INSERT INTO MyDB..currently_running_jobs
EXECUTE [192.168.xx.xx].master.dbo.xp_sqlagent_enum_jobs 1,''I get this error:
OLE DB provider "SQLNCLI11" for linked server "192.168.xx.xx" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 69
The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "192.168.xx.xx" was unable to begin a distributed transaction.
Is the Distribute Transaction Coordinator(MSDTC) service running on both of the servers?
Sue
June 26, 2018 at 2:53 pm
Yes, DTC active on both servers.
June 26, 2018 at 2:57 pm
Right click the linked server and select properties. Set RPC, RPC Out and Enable Promotion of Distributed Transaction to true.
June 26, 2018 at 3:01 pm
Already set to TRUE.
The Linked Server seems ok, until I try to insert the results into a local table.
It worked years ago when we have physical servers at our hosting site, running SQL 2005.
Now VM servers, with stricter firewalls, running SQL 2016.
June 26, 2018 at 3:05 pm
homebrew01 - Tuesday, June 26, 2018 3:01 PMAlready set to TRUE.
The Linked Server seems ok, until I try to insert the results into a local table.
So you have it configured the same as this screen shot:
Configure Microsoft Distributed Transaction Coordinator (MSDTC)
You may also want to verify things between the two servers with DTCTester:
How To: Use DTCTester
Sue
June 26, 2018 at 3:17 pm
Sue_H - Tuesday, June 26, 2018 3:05 PMhomebrew01 - Tuesday, June 26, 2018 3:01 PMAlready set to TRUE.
The Linked Server seems ok, until I try to insert the results into a local table.So you have it configured the same as this screen shot:
Configure Microsoft Distributed Transaction Coordinator (MSDTC)You may also want to verify things between the two servers with DTCTester:
How To: Use DTCTesterSue
I did not have those settings but do now.
I get a different error after 10 seconds:
OLE DB provider "SQLNCLI11" for linked server "192.168.xx.xx" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 69
The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "192.168.xx.xx" was unable to begin a distributed transaction.
June 26, 2018 at 3:28 pm
homebrew01 - Tuesday, June 26, 2018 3:17 PMSue_H - Tuesday, June 26, 2018 3:05 PMhomebrew01 - Tuesday, June 26, 2018 3:01 PMAlready set to TRUE.
The Linked Server seems ok, until I try to insert the results into a local table.So you have it configured the same as this screen shot:
Configure Microsoft Distributed Transaction Coordinator (MSDTC)You may also want to verify things between the two servers with DTCTester:
How To: Use DTCTesterSue
I did not have those settings but do now.
I get a different error after 10 seconds:OLE DB provider "SQLNCLI11" for linked server "192.168.xx.xx" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 69
The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "192.168.xx.xx" was unable to begin a distributed transaction.
One of the other 80 million settings for this....is Enable Promotion of Distributed Transactions set to True for the linked server? It should be...I think it's the default.
Also, if you've made changes to the service, restart it.
Sue
March 20, 2019 at 10:10 am
Was this ever resolved? we are also facing this issue on SQL 2012, the same code was working until 3 hours ago and now it doesnt without any change.
May 31, 2021 at 1:34 pm
Please set Enable Promotion of Distributed Transactions to False
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply