Audit use of CLR Table-valued function

Question

I have written some CLR table-valued functions that invoke a web service and I would like to audit their use. Does anyone know of any way this can be achieved in the CLR code itself? I would be nice to keep the auditing code together with the CLR code rather than having to write an explicit INSERT statement every time they are used.

Answer 1

Since the TVF calls a Web Service, the Assembly had to be marked as WITH PERMISSION_SET = EXTERNAL_ACCESS (hopefully you chose to sign the Assembly, create an Asymmetric Key, and create a Login from that Key, as opposed to setting TRUSTWORTHY ON for the Database). This fact gives you two options:

1. Use something like File.AppendAllText to log the info into a file and keep it out of the database. This method creates the file if it doesn't exist. NOTE: This option needs to be tested with concurrent actions that would fire the Trigger as there might be some blocking / additional wait-time incurred if the OS does not allow concurrent write activity on the file.

2. Log to a table by creating an regular / external SqlConnection and issuing an INSERT statement. Be sure to specify enlist=false; in the Connection String so that it doesn't attempt to bind to the current Transaction. This will allow the INSERT (which occurs in a separate connection / Session) to happen whether or not there is an exception raised in the SQLCLR TVF (unless, of course, the error is with the INSERT operation ;-).