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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply