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:
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.