May 16, 2014 at 11:56 am
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.
May 16, 2014 at 1:07 pm
It is well expalined in this blog.
Check the appendix and be sure you are disabling "remote proc transaction promotion" and enabling "rpc out".
Good luck!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply