You probably heard about autonomous transactions in Oracle. In a few words, using just one pragma directive in a PL/SQL block you can isolate it from the callers’ context, so it becomes independent transaction. It’s quite useful if you want organize data audit and is pretty widely used in production databases. As you might guess these autonomous transactions do not have direct equivalent in Microsoft SQL Server. The only way to isolate a Transact-SQL block from a transaction context is to open a new connection. There are several options here, so let’s start with SSMA approach.
Assume that we have some logging logic in a stored procedure named “dbo.LogError” and we want to commit it even in case when main transaction will be roll backed.
As in previous case with UDF exceptions SSMA use extended procedure (now – to open a new connection). SSMA will wrap our routine like that:
1 2 3 4 5 6 7 | EXECUTE master.dbo.xp_ora2ms_exec2_ex @active_spid, @login_time, N'TEST_AT', N'DBO', N'LogError$IMPL', N'false' |
It was the only way to do that in SQL Server 2000 and it still works faster than other solutions, but there is a one big issue here – Microsoft announced that this feature is officially deprecated, so extended stored procedure is just not an option now.
Starting from SQL Server 2005 we can use CLR instead and in SQL Server 2008 version loopback linked server is appropriate too. You can read more about loopback approach in this article.
It’s pretty good primarily because it’s very simple. Once you have configured loopback server all you need is just change invocation T-SQL code from
1 | exec dbo.LogError |
to
1 | exec loopback.DatabaseName.dbo.LogError |
without any changes in target routine. But you have to keep in mind that using linked server in SQL Server always negatively affects performance. Calling routine throw the linked server is about 3-4x times slower than same invocation in current context. Well, it’s actually 1-2 milliseconds overhead per execution (or even less), but still, if you looking for a solution for very high loaded system, maybe you need something else.
SQLCLR might be good enough (this approach is recommend by Microsoft by the way). It still will affect performance, but a little less (up to 2-2.5x times slower). And if there are some computational tasks in your logic, CLR can provide additional performance here and maybe compensate calling overhead. Of course, writing CLR routines requires knowledge of C# or other .NET language. But if we just want to execute our stored procedure in new connection using CLR, .NET code will be very simple. If you are new to SQLCLR, you can start with this article.
Code of CLR wrapper for our “LogError” stored procedure will look like that:
1 2 3 4 5 6 7 8 9 10 | [Microsoft.SqlServer.Server.SqlProcedure] public static void ErrorLoggingCLR() { using(SqlConnection connection = new SqlConnection("context connection=true")) { connection.Open(); SqlCommand command = new SqlCommand("exec dbo.LogError", connection); command.ExecuteNonQuery(); } } |
After you deploy this to SQL Server, you can call it just like usual SQL Server stored procedure. Just make sure that your server is configured properly (sp_configure ‘clr enabled’ returns “1” as running value).
