I was working on a different blog post this morning when I happened upon this little problem. I was trying to implement statement level auditing of a database without using a SQL Trace in SQL Server 2005 for SOX Auditing, very similar to what we can do with the Server Audits in SQL Server 2008, when I happened upon a couple of posts on the forums that I had responded to in the past. The first was a post asking how to do this very thing where I posted to use a SQL Trace, ironic I know, but later a post was made with a link to a "Oriental/Asian" (trying to be politically correct because I can't tell if the characters are Chinese or Japanese, but I suspect Chinese because it is at http://blogs.csdn.net/ which looks more like it should be Chinese) blog that shows how to accomplish the task using DBCC INPUTBUFFER.
That is great, and the code works out perfectly, but I am on SQL 2005, so I should be able to just hit up the DMV's for this information right? Actually the answer is NO YOU CAN'T. I should have recalled this from last February when UC from Microsoft replied to the following post:
Alternative to DBCC INPUTBUFFER/sys.dm_exec_sql_text?
I tried to use a number of the DMV's, sys.dm_exec_requests, sys.dm_exec_connections, sys.sysprocesses, and all of them return, the code of the trigger when run inside of the trigger. I don't get how something this simple was overlooked, especially when the DMV's are supposed to be our replacement for the older code and structures, or are they? The BOL entry for DBCC INPUTBUFFER doesn't have the standard deprecation announcement that can be found on other items that the DMV's should replace, so it really isn't intended in the current releases at least for the DMV's to be able to replace this functionality.
The good news is that according to UC, Microsoft is aware of this and working to fix it.
Here's a Connect item to vote on:
ReplyDeletehttp://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=322262
I saw that connect, but it is slightly different in purpose than what I am demonstrating in this post. It is an important connect item nonetheless. I am curious to see how Microsoft's solution to that item will work inside a trigger which is a nested operation.
ReplyDeleteDBCC INPUTBUFFER returns the command called by the client, but I can see where the client calls a stored procedure that has a nested stored procedure call in it, one might want to know the context information of the nested call, and not necessarily the command that was sent by the client, especially if a subsequent calling of the stored procedure would result in a different set of parameters for the nested stored procedure call.