Error logging or custom logging is a very common requirement while working with any database & its operations; irrespective of the database server type i.e. be it Oracle or SQL Server database. And, when we talk about SQL Server, it gives great features to deal with the errors/exceptions by means of TRY & CATCH blocks into the SQL Stored Procedures and performing the operations inside some Transactions.
So, pseudocode for the basic structure of any exception handling inside a Stored Procedure is like as below –
This looks very promising, appears to be handling almost all of the exceptions & transactions related requirements and a prompt answer if anybody is asked about error logging in SQL Server database.
As mentioned above, in case of any exception the current transaction will be rolled back & then we can make it to perform certain other required auditing operations (inside the catch block) like logging the custom error into some T_Error table and then finally raising/throwing that error to the calling code. However, when we talk about the event logging which is not constrained to just the error logging but all the events we want to capture during the execution of various statements inside a stored procedure e.g. at the beginning of the stored procedure, at the start of some particular statement, at the end of the procedure etc. And to implement the same we can probably create an EventLogging procedure (may be working inside its own transaction) which can be called at any step, as stated above, inside a stored procedure, and also in the catch block to log the error in case of any exception. All good so far.
Let’s make the situation a bit more complicated. If we look at the code block depicted above more carefully, we would get a question in our mind as to what will happen if we have done loads of custom logging inside a stored procedure & then suddenly we get some unwanted exception and at the end everything is rolled back – everything includes logging done as well :(. Moreover, if this procedure is being executed inside some other parent procedure and in case of any exception into this child proc, it will not only rollback the child proc’s transaction (including logging done) but also of the parent proc. To understand this, one needs to be aware of the fact that the outermost commit is what controls the inner commits and any of inner rollback will rollback all the whole transaction.
So, at this point we have understood the problem & the risk that the event/error logging done inside a stored proc can be lost at the end if we rely on the transactions opened by the current or the parent proc. Hence, you all would agree that we should be doing something to operate inside some independent/autonomous transaction while logging so that error/event messages are logged despite the transaction is doing a rollback. But the bad news here is that In Microsoft SQL Server, there is no direct equivalent for this. There is a difference between nested and autonomous transaction.
In Oracle there is a straight forward way of doing this by means of Pragma Directive, but in SQL Server we need to go for some workarounds to achieve Autonomous Event/Error logging like using loopback approach which is about setting up a linked server & RPC call, but not very efficient in nature and another approach is using the CLR stored procedure – a recommended approach.
In this article, we would achieve Autonomous logging into SQL Server using CLR Stored Procedure. Here, we wouldn’t be going into the detailed working of SQLCLR stored procedures.
- Let’s assume we have a SQL stored procedure ‘usp_EventLog’ written for logging the events (start, info, error, end etc) into eventLog table (say dbo.T_EventLog).
- This proc has got its own transaction – commit & rollback
- We can pass EventType (ID), id of the calling parent proc and the message etc to be logged into the EventLog table.
Now, create a CLR stored procedure (using visual studio) which will act as a wrapper and will execute the SQL SProc in a separate connection i.e. an autonomous logging. We are setting it up for two parameters at the moment, CallingProc’s name (string) EventLogTypeID (Int), EventMessage (String). These parameters should in line with the underlying SQL procedure.
String datatype of CLR procedure maps to NVARCHAR of the SQL Server datatype.
This CLR stored proc needs to be registered into SQL Server assembly and then a SQL procedure needs to be defined to make a call this assembly proc or this CLR procedure can directly be deployed using an option into Visual Studio itself.
So, finally we get to create another SQL procedure named ‘dbo.usp_CLR_AutonomousLogging’ which will in turn refer the CLR assembly. And while event logging inside any SQL procedure etc, this proc should be called passing the required parameters. This will ensure to be executed in a separate autonomous transaction and event logging will never be rolled back even if the calling proc fails.
Make sure CLR execution is configured into the SQL Server, if not then below script can be run to enable it.
…calling the CLR proc