OLE DB provider "SQLNCLI10" for linked server "servername" returned message "No transaction is active.".

• satish_kukunuru

  Old Hand

  Points: 345

  More actions

  January 22, 2012 at 11:18 pm

  #391371

  I am facing this problem while calling a stored procedure which is there in linked server.

  My purpose of calling stored procedure is to get data from remote server and inserting into temporary table in my server.

  I have checked settings in both the servers ( version is SQL server 2008) and I have done the following settings.

  1) Allow remote clients,

  2) Allow remote administration

  3) allow inbound

  4) allow outbound

  5) no authentication required

  6) Enable XA transactions

  These all are enabled.

  Still facing "No transaction Message" issue.

  Can any one of you please help me in resolving this issue.

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  January 23, 2012 at 1:47 am

  #1436970

  Might be a totally different thing, but this article[/url] shows you how to set up and troubleshoot linked servers and OLEDB providers.

  Hope this helps

  Gianluca

  -- Gianluca Sartori

• satish_kukunuru

  Old Hand

  Points: 345

  More actions

  January 23, 2012 at 2:05 am

  #1436972

  Thanks for replying. The article given by you is much informative.

  Still problem didn't resolve.

  In my case, the problem is about the distributed transaction.

  "SQLNCLI10" for linked server "servername" was unable to begin a distributed transaction.

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  January 23, 2012 at 2:11 am

  #1436975

  Did you reboot the server(s) after tweaking MSDTC configuration? This step is required.

  How is the provider loaded? In process or out of process?

  -- Gianluca Sartori

• satish_kukunuru

  Old Hand

  Points: 345

  More actions

  January 23, 2012 at 2:41 am

  #1436987

  http://www.sqlservercentral.com/articles/Linked+Servers/73794/

  The above is the link given by you for the configuration.

  I didn't try the MMC configuration, cluster configuration. I did only MSDTC changes.

  do I need to give port number as exception in the firewall ? Can you clarify me what is in process & our process mean ? I am new to this area using remote calls.

  In the Link server, i have stored procedure. By calling linked server stored procedure, i need to insert the data into a temporary table which is declared in my local server. This is my requirement.

  Thanks

  Satish

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  January 23, 2012 at 3:19 am

  #1436996

  satish_kukunuru (1/23/2012)

  ---

  Can you clarify me what is in process & our process mean ? I am new to this area using remote calls.

  Run this:

  EXEC master.sys.sp_MSset_oledb_prop

  What does this return for SQLNCLI10, column "allow_in_process"?

  In process means that the provider gets loaded into SQLServer's address space, out of process means that the provider gets loaded outside SQLServer's address space by a surrogate process.

  do I need to give port number as exception in the firewall ?

  Maybe. See if DTCPING helps you identifying network related issues.

  -- Gianluca Sartori

• Jared

  SSC Guru

  Points: 61793

  More actions

  January 23, 2012 at 9:25 am

  #1437197

  What are your Linked Server options set to for this specific linked server object?

  Jared
  CE - Microsoft