loopback linked server - Autonomous transaction

• AVDR

  SSC-Addicted

  Points: 418

  More actions

  May 16, 2014 at 11:56 am

  #307464

  Hi,

  I am implementing autonomous transactions using loopback linked server on SQL Server 2012.

  I have a primary server named "server1" and required objects under "DB1". I have all procedures and logging tables on "DB1". Procedures needs to be executed to insert data into logging tables.

  I have created a loopback linked server on same server(server1) but named it as "Server2". A new Database is created in "Server2" named "DB2"

  Now when i execute "select * from [Server2].DB2.sys.tables" from "Server1", it works well. This means, i can use the linked server connection is working.

  But when i execute "[Server2].DB1.sys.tables", i get error message saying table is not available.

  I am under the assumption that a loopback linked server is "Executing a query on Server 1, will open a distributed transaction on server 1, loopback to server 1 and access server 1 tables".. But it appers this is not true. I am confused with term LOOPBACK.

  If this doesn't work, that means should i have logging tables and Store procedure to load logging tables on Linked Server database...

  I appreciate your responses.

• Hunchback

  SSCrazy

  Points: 2649

  More actions

  May 16, 2014 at 1:07 pm

  #1714321

  It is well expalined in this blog.

  http://blogs.msdn.com/b/sqlprogrammability/archive/2008/08/22/how-to-create-an-autonomous-transaction-in-sql-server-2008.aspx

  Check the appendix and be sure you are disabling "remote proc transaction promotion" and enabling "rpc out".

  Good luck!