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.
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:
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.
Log to a table by creating an regular / external
SqlConnectionand issuing an
INSERTstatement. 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