2

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.

2

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 ;-).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.