May 14, 2014 at 8:23 pm
Hi,I know Oracle provide pragma directives to execute autonomous transactions which i used before on Oracle for logging. Now i want to repeat the same in SQL Server but unfortunately i found such pragmas are not existing in SQL Server.
After several google searching, i have found that i can use loopback linked server to generate autonomous transaction calls.
If i have Server A & Server B where server B is a loop back server of Server A and all my objects are existing on Server A. I just wanted to user Server B for logging only.
To achieve this, should i have logging tables on Server B? Logging procedures on Server A? and call logging procedures (via Execute ) from application procedures residing on Server A?
I appreciate your responses.
May 14, 2014 at 11:37 pm
To be honest, rather don't.
What you can do is insert your logging records into a table variable and then insert the contents of the table variable into the permanent logging table after the transaction completes (commit or rollback)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 15, 2014 at 2:13 am
Another possible solution is a CLR stored procedures that instanciates a connection without enlisting in the current DTC context.
Paul White has a nice blog post on the subject here. Look for the Simulating Autonomous Transactions section.
I would never use the loopback linked server in production: it's looking for trouble.
If you can't achieve your goals with Gail's suggestion, I would rather look into CLR.
-- Gianluca Sartori
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy