October 24, 2012 at 2:24 pm
Hi,
I just sent up an audit on a database to collect all the select queries on a table. Everything is working fine if we use just select statmenet with where condition. But if we use a variable it's not capturing the variable value. It just grabs the variable name. How to overcome this? I am using 2008 sp3 enterprise edition.
October 25, 2012 at 7:28 am
Try 'Change Data Capture', it can capture DML statements
October 25, 2012 at 12:03 pm
Is there any other way we can do it other than CDC?
October 25, 2012 at 12:28 pm
Server-side traces and extended events are how I do that kind of thing. You can capture any DML events you want to, and filter them pretty much however you like, using either of those.
I recommend extended events, because Microsoft says they'll sunset traces in a future version of SQL Server and to use extended events for new development.
Data on the subject here: http://msdn.microsoft.com/en-us/library/dd822788(v=SQL.100).aspx
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 25, 2012 at 9:55 pm
As Gus suggested, a server side trace with a LIKE filter looking for the table name will work quite nicely. So would "extended events".
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply