Thanks
[quoted text, click to view]
When you say "all the records accessed", do you mean just the rows =
changed (INSERT, UPDATE, DELETE) or those that have been read (SELECT) =
as well?

If you're just talking about changes to the data then you can add a =
trigger to the table to update your "timestamp" column each time a row =
is updated (and give that column an initial value of CURRENT_TIMESTAMP =
(same as getdate() function) when you first INSERT the row, using a =
DEFAULT CONSTRAINT in your table schema). The data deletions would be a =
little trickier - in the delete trigger you'd have to INSERT the =
"before" version of the row being deleted into some kind of holding =
table (so you can report on it later).

If you're talking about auditing data reads as well as data writes, =
the bottom line is you cannot do it (unless you use SQL Profiler). This =
is because there is no record of what data gets read (transaction log =
only records data modifications) and no event in the database engine =
that can fire in response to a read (ie. no SELECT trigger). The only =
way to accomplish that level of audit reporting is to use SQL Profiler =
to watch the statements hitting the SQL server and save that info away =
in a file or table (that you can later report against). You can set up =
a filter in SQL Profiler to just pay attention to the relevant table by =
specifying a "like '%MyTable%'" filter on the text column of the trace =
(and maybe a "=3D <dbid>" filter on the dbid column) - it's not =
fool-proof but it should at least get you 99% of the way there.

Of course, setting up a continual profiler trace against your server =
will drag down performance a little depending on a myriad of factors - =
how grunty your box is, what kind of load it's under, what the network =
traffic is like, where you're saving the trace info, level of activity =
against the DB, etc., etc.

--
mike hodgson
http://sqlnerd.blogspot.com=20



dthmtlgod wrote:=20
I am pretty new to SQL Server, I believe we run SQL Server 2000.

I have a table with about 150,000 records that get accessed everyday. I =

need to export a list of all the records accessed during the day. I =
have a=20
field that timestamps each update.

What is the easiest and most convenient way for me to do this. I would =
like=20
the data to be exported to a text file in an automated fashion.

Any assistance is much appreciated.