My Favorite Blogs

Thursday, December 29, 2011

Msg 7391, Level 16, State 2, Line 8


Today I worked with a developer who was trying to query some data from a remote instance and inserting into a local instance, he told me that he created the linked server locally for the abc-sqlops01 instance and made sure that all fine. And also he specified that the test connection of the linked server was succeeded and simple SELECT statement is working fine but when he tried to run a stored procedure he is getting the errors as bellow.

Executed statement:

INSERT INTO datalist
EXEC [abc-sqlops01].Updatelist.dbo.[GetUpdatelist] 'opstestdata'

Error:

OLE DB provider "SQLNCLI" for linked server "abc-sqlops01" returned message "The partner 
transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 8
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "abc-sqlops01" was unable to begin a distributed transaction.



Work Around:

 After digging though found that this sproc is creating a temp table on the linked server and inserting data on it and he is inserting that data locally. This action is validated by MSDTC security settings, which I found not set.  Once it fixed the stored procedure executed without any errors.

Start àAdministrative Tools àComponent Services
This will open the Component Services console
Expand the Component Services àDistributed Transaction Coordinator
                Right click on the Local DTC àProperties
                On the Local DTC Properties window, click on the Security Tab
                                Check Network DTC Access under Security Settings

                                In the Transaction Manager Communication box, Check Allow Inbound and Allow Outbound  (or check either one based on your business requirement)  and check the required Authentication method as well as required. In my case I checked “No Authentication Required”.

Refer below screen shot for reference.