Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
leezo
Starting Member
9 Posts |
 Posted - 2009-06-24 : 09:24:31
|
| Hi,Is it possible to create a trace for auditing queries against a certain table by using TSQL and post the result in a table like you do in the Profiler?If the result of the trace cannot be saved in a table, how can I enable file rollover in TSQL?Thanks |
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2009-06-25 : 13:54:15
|
quote:
Originally posted by leezo
Hi,Is it possible to create a trace for auditing queries against a certain table by using TSQL and post the result in a table like you do in the Profiler?If the result of the trace cannot be saved in a table, how can I enable file rollover in TSQL?Thanks
Yes, but it will take a few steps to do. We do this exact same thing at our organization. I'll give you a basic overview of what you will need. First, review this article as it discusses server side tracing and collection: [url]http://technet.microsoft.com/en-us/library/cc293613.aspx[/url].Once you read that article you should take the resulting code and make it a stored procedure that is marked as an auto start procedure. I have mine to maintain 20-5 MB files.Next, create a table in the database that maps to every column the trace captures (mine is a bit more robust as it will map to any column possible in any trace but that's another topic). Then create a SQL Server job that imports the data from the rollover files into the table and drops the rollover files from the file system.That is it in a nutshell. |
 |
|
|
|
|
|
Trace for Audit